Implementation of MySQL custom list sorting by specified field

Problem Description

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

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

The SQL for 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 it? This is also one of the problems often encountered by many domestic and foreign counterparts.

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

Solution

Use "ORDER BY FIELD".

grammar

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

Note 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 article on how to implement MySQL custom list sorting by specified fields is all I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.