How to sort the results of MySQL query
How to sort the MySQL query results, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.
How to sort the results of MySQL query
Previously, there was a feature modification that required MySQL query results to include:
Idname***
1lucy...
3lucy...
2lily...
4lucy...
The priority named lucy is at the top of the list, which is puzzling. Some people may say simple union or get a temporary table or something. In fact, I have also thought about it, but there is a lot of SQL logic (just a simple example above), and then union or temporary table may take a big detour. Later, I saw an article trying to add orderbyfind_in_set (name,'lucy'), and the result was that lucy was all below. Then I changed to orderbyfind_in_set (name,'lucy') desc implementation and the result is
Idname***
1lucy...
3lucy...
4lucy...
2lily...
Basic implementation, but a little uncertain mood, check the mysql document to find find_in_set syntax
How to sort the results of MySQL query
FIND_IN_SET (str,strlist)
If the string str is in the string column data table strlist consisting of N subchains, the range of return values is between 1 and N. A string column data table is a string made up of self-chained characters separated by "," symbols. If the first argument is a constant string and the second is a typeSET column, the FIND_IN_SET () function is optimized to be calculated in bits. If str is not in strlist or strlist is an empty string, the return value is 0. If any parameter is NULL, the return value is NULL. This function will not work properly when the first argument contains a comma (",").
Mysql > SELECTFIND_IN_SET ('bachelors, page1, pr, pas, c,');-> 2
After reading this, I estimate why desc is added to the result. Find_in_set returns its position when there is a lucy, 0 when it is not available, and null when it is empty, so it is sorted as 1, 1, 1, and 0, and if added to the column, it is 0.
IdnameFIND_IN_SET**
1lucy1...
3lucy1...
2lily0...
4lucy1...
The table structure is as follows:
Mysql > select*fromtest;+----+-+ | id | name | 1 | test1 | | 2 | test2 | 3 | test3 | 4 | test4 | | 5 | test5 | +-+-+
Perform the following SQL:
Mysql > select*fromtestwhereidin; +-+-+ | id | name | +-+-+ | 1 | test1 | | 3 | test3 | | 5 | test5 | +-+-- + 3rowsinset (0.00sec)
The results of this select in mysql are automatically sorted in ascending order of id.
But I want to order the results of "select*fromtestwhereidin" according to the conditions in in, that is, 3Magi 1JI 5. The desired results are as follows:
Idname
3test3
1test1
5test5
The methods are as follows:
Select*fromtestwhereidin (3Jing 1jue 5) orderbyfind_in_set (id,'3,1,5'); select*fromtestwhereidin (3Jing 1JEI 5) orderbysubstring_index ('3Jing 1Jing 2Jing idjue 1)
Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.
