A brief discussion on Mysql specified order sorting query

Recently, I have been working on a large-screen display project, similar to the one that displays flight information at airports and train stations, but the content displayed is much more complicated. Some of the data are as follows:

The front-end mainly uses the Vue framework, requiring the back-end data to be displayed on the front-end.

First: The factory entry and exit indicators are not fixed (may be 6, or may be 7 or 8 in the future);

Second: Use slashes instead of "numeric" values;

Third: The names of the inbound and outbound indicators must conform to the names specified in the figure above (the names queried from the back-end database are different from the names specified in the front-end, and the units contain ">1", ">0.3", etc., so they cannot be queried directly from the back-end. The name + unit is placed on the front-end and needs to be processed by yourself); Fourth: The order in which the back-end data is transmitted must be the order specified by the front-end. Anyway, the requirement is that the front end only needs to loop out the data, and the back end takes care of all other needs.

There are two tables: table_a, table_b

Step 1: Create a table based on table_a.id=table_b.ids

SQL statement: SELECT * FROM (SELECT * FROM table_a a INNER JOIN table_b b ON a.id=b.ids)A, take the underlined part as a new table A

Step 2: It is found that Xiaobailong and Sha Wujing are not in the database, so they need to be added:

SQL statement:

SELECT * FROM (SELECT * FROM table_a a INNER JOIN table_b b ON a.id=b.ids
UNION ALL
SELECT 8 id,'白龙马' `name`,'小白' sname,'1000' age ,8 ids, 27 `value`
UNION ALL
SELECT 9 id,'Sha Wujing' `name`,'Juanlian General' sname,'2000' age ,9 ids, 289 `value`
)A

The underlined part is used to add some missing data, which is sometimes used for fixed display needs on the front end.

Step 3: Please arrange in the order of Tang Monk, Sun Wukong, Zhu Bajie, Sha Wujing, Xiao Bailong, Chunhua, Wang Wu, Zhao Liu, Tang Qi

SQL statement:

SELECT * FROM (SELECT * FROM table_a a INNER JOIN table_b b ON a.id=b.ids
UNION ALL
SELECT 8 id,'白龙马' `name`,'小白' sname,'1000' age ,8 ids, 27 `value`
UNION ALL
SELECT 9 id,'Sha Wujing' `name`,'Juanlian General' sname,'2000' age ,9 ids, 289 `value`)A
    INNER JOIN (SELECT 7 ids, 1 `order`
UNION ALL
SELECT 2 ids, 2 `order`
UNION ALL
SELECT 4 ids, 3 `order`
UNION ALL
SELECT 9 ids, 4 `order`
UNION ALL
SELECT 8 ids, 5 `order`
UNION ALL
SELECT 6 ids, 6 `order`
UNION ALL
SELECT 1 ids, 7 `order`
UNION ALL
SELECT 5 ids, 8 `order`
UNION ALL
SELECT 3 ids, 9`order` ) B ON B.ids=A.id
ORDER BY B.order

The underlined statements add two fields to the entire table A, thereby achieving a fixed sort in a specified manner. Fields can also be added to achieve other desired purposes.

The above is all the content of our introduction to MySQL specified order sorting query. If you still have any questions, you can discuss in the comment area below.