Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.