How Can I Order SQL Query Results by Multiple Values in a Specific Sequence?

Sort multiple values ​​in a specific order

In SQL, query results can be sorted by multiple values, but only if these values ​​must be included in the ORDER BY clause. If you try to sort by multiple values ​​that are not included in the ORDER BY clause (as in the provided example), the query will return no results.

To sort multiple values ​​in a specific order, it is recommended to use the CASE statement:

<code class="language-sql">...
WHERE
   x_field IN ('f', 'p', 'i', 'a') ...
ORDER BY
   CASE x_field
      WHEN 'f' THEN 1
      WHEN 'p' THEN 2
      WHEN 'i' THEN 3
      WHEN 'a' THEN 4
      ELSE 5 -- 对IN子句中未包含的值的回退。例如：x_field = 'b'
   END, id</code>

In this example, the CASE statement assigns a numeric priority to each value in the x_field column ('f' is 1, 'p' is 2, and so on). The results are then sorted based on this priority and then by the id column to ensure a unique sorting within each priority level.

By using the CASE statement, you can specify the desired sort order for multiple values. In this example, the results will be sorted in the order 'f', 'p', 'i' and 'a', as specified in the CASE statement.

The above is the detailed content of How Can I Order SQL Query Results by Multiple Values in a Specific Sequence?. For more information, please follow other related articles on the PHP Chinese website!