Talking about Mysql specified ordered query, talking about mysql ordered Query
Recently, I made a big screen presentation project, similar to displaying shift information at airports and railway stations. However, the presentation content is much more complicated. Some of the data is as follows:
The front-end mainly uses the Vue framework and requires that the back-end data be displayed on the front-end,
First, the factory indicators are not fixed when entering the factory (may be 6, or may be 7 or 8 in the future );
Second, use a slash instead of a value without a value;
Third, the name of the import/export metric must comply with the specified name (the name queried by the backend database is different from the name specified by the frontend, and the unit contains "> 1 ", "> 0.3" and so on, so you cannot directly query data from the backend. The name + unit is placed on the front end and must be processed by yourself. 4: the order of backend data transmission must be specified at the front end. The requirement is that the front-end only needs to loop out data, and the backend can handle all the other requirements.
There are two tables: table_a and table_ B.
Step 1: merge a table according to table_a.id = table_ B .ids
SQL statement: SELECT * FROM (SELECT * FROM table_a inner join table_ B B ON a. id = B. ids) A. The underline is used as the new table.
Step 2: find that the database does not contain Xiao Bailong and Sha Wujing. You need to add the following:
SQL statement:
SELECT * FROM (SELECT * FROM table_a inner join table_ B B ON. id = B. idsUNION ALLSELECT 8 id, 'White longmar' 'name', 'xiaobai' sname, '000000' age, 8 ids, 27 'value' union allselect 9 id, 'sha Wujing 'name', 'roller shutter generals 'sname, '000000' age, 9 ids, 2000 'value')
The dash part is used to add some missing data, which is sometimes required for fixed front-end display.
Step 3: sort by Tang Seng, Sun Wukong, Zhu Bajie, Sha Wujing, Xiao Bailong, Chunhua, Wang Wu, Zhao Liu, and Tang Qi.
SQL statement:
SELECT * FROM (SELECT * FROM table_a inner join table_ B B ON. id = B. idsUNION ALLSELECT 8 id, 'White longmar' 'name', 'xiaobai' sname, '000000' age, 8 ids, 27 'value' union allselect 9 id, 'sha Wujing 'name', 'roller shutter generals 'sname, '000000' age, 9 ids, 2000 'value') a inner join (SELECT 7 ids, 1 'order' union allselect 2 ids, 2 'order' union allselect 4 ids, 3 'order' union allselect 9 ids, 4 'order' union allselect 8 ids, 5 'order' union allselect 6 ids, 6 'order' union allselect 1 ids, 7 'order' union allselect 5 ids, 8 'order' union allselect 3 ids, 9 'order') B on B. ids =. idORDER by B. order
The underlined statement adds two fields to the entire table A to achieve the fixed sorting according to the specified method. At the same time, you can add fields to achieve other purposes.
The above is all the content of the Mysql sorted query in the specified sequence. If you have any questions, you can discuss it in the comment area below.