MySQL Sort by specified field custom list

[Turn]http://www.php42.com/index.php/database/35-sql/237-mysql-order-by-list

Problem description

As we all know, MySQL in the order of a word orderby sequence of SQL (in the case of ID, the following):

SELECT * from ' MyTable ' WHERE ' id ' in (1, 7, 3, 5) ORDER by ' id ' ASC

The descending sort of SQL is:

SELECT * from ' MyTable ' WHERE ' id ' in (1, 7, 3, 5) The ORDER by ' id ' DESC

Sometimes the above sort does not meet our needs. For example, we want to sort by ID in the order of 5, 3, 7, 1, and how. This is also a lot of domestic and foreign counterparts frequently encountered one of the problems.

Here's a solution to sort by a field in the table, sorted by the list we want.

Solution Solutions

Sort by fields (order by field).

Grammar

ORDER by FIELD (' ID ', 5, 3, 7, 1)

It is important to note that there is no space behind the FIELD .

Therefore, the full 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 (' Zhang San ', ' John Doe ', ' Harry ', ' Sun Six ') ORDER by FIELD (' name ', ' John Doe ', ' Sun Six ', ' Zhang San ', ' Harry ')

Resources

Handy Mysql-order by FIELD

Sort results by order in list

MySQL Sort by specified field custom list