How to specify ordered sort query in Mysql

Editor to share with you how to specify the order sort query in Mysql, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Examples are as follows

The front end mainly uses the Vue framework, which requires the back end data to be displayed in the front end.

First: the index of entering the factory and leaving the factory is not fixed (maybe 6, or 8 in the future)

Second, if there is no "numerical" value, use a slash instead.

Third: the name of the factory indicator must be in line with the name specified in the figure above (the name queried by the back-end database is different from the name specified by the front end, and the unit has "> 1", "> 0.3" and so on. Therefore, you cannot query directly from the back end. The name + unit is placed at the front end and needs to be handled by yourself. Fourth: the order in which the back-end data is transmitted must be specified by the front end. Anyway, the requirement is that the front end only needs to cycle out the data, and the back end takes care of all the rest of the requirements.

There are two tables: table_a,table_b

Step 1: synthesize a table according to table_a.id=table_b.ids

Sql statement: SELECT * FROM (SELECT * FROM table_a an INNER JOIN table_b b ON a.id=b.ids) A, using the underscore as a new table A.

Step 2: find that there are no Xiao Bailong and Sha Wujing in the database. You need to add:

Sql statement:

SELECT * FROM (SELECT * FROM table_a an INNER JOIN table_b b ON a.id=b.idsUNION ALLSELECT 8 id,' Bailongma '`name`,' Xiaobai 'sname,'1000' age, 8 ids, 27' value`Union ALLSELECT 9 id,' Sha Wujing '`name`,' Curtain General 'sname,'2000' age, 9 ids, 289`value`) A

The underlined part is to add some missing data, which is sometimes needed for the front-end fixed display.

Step 3: please sort according to 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 an INNER JOIN table_b b ON a.id=b.idsUNION ALLSELECT 8 id,' White Dragon Horse '`name`,' Xiaobai 'sname,'1000' age, 8 ids, 27 `value`Union ALLSELECT 9 id,' Sha Wujing' `name`, 'Curtain General' sname,'2000' age, 9 ids, 289 `value`) An 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=A.idORDER BY B.order

This kind of statement in the underlined part adds two fields to the entire A table, and then achieves the purpose of fixed sorting according to the specified way; at the same time, it can also add fields to achieve other desired purposes.

The above is all the contents of the article "how to specify ordered sort queries in Mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!