Detailed introduction to MySQL implementation of custom list sorting by specified fields

The following editor will bring you an articleMySQLThe implementation of custom list sorting by specified fields. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor to take a look.

Problem description

As we all know, the SQL to sort a field in ascending order in MySQL is (with id as an example, the same below):

SELECT * FROM `MyTable` 
WHERE `id` IN (1, 7, 3, 5) 
ORDER BY `id` ASC

The SQL in descending order is:

SELECT * FROM `MyTable` 
WHERE `id` IN (1, 7, 3, 5) 
ORDER BY `id` DESC

Sometimes the above sorting does not meet our needs. For example, we want to sort by id in the order of 5, 3, 7, 1, how to achieve this. This is also one of the problems that many colleagues at home and abroad often encounter.

Below we give a solution to sort by a certain field in the table in the list format we want.

Solution

Use "ORDER BY FIELD".

Syntax

ORDER BY FIELD(`id`, 5, 3, 7, 1)

It should be noted that there is no space after FIELD.

Therefore, the complete SQL is:

SELECT * FROM `MyTable` 
WHERE `id` IN (1, 7, 3, 5) 
ORDER BY FIELD(`id`, 5, 3, 7, 1)

Common applications

SELECT * FROM `MyTable` 
WHERE `name` IN ('张三', '李四', '王五', '孙六') 
ORDER BY FIELD(`name`, '李四', '孙六', '张三', '王五')

The above is the detailed content of Detailed introduction to MySQL implementation of custom list sorting by specified fields. For more information, please follow other related articles on the PHP Chinese website!