How Can a CASE Expression Order SQL Results by Specific Values?

Use CASE expressions to sort SQL results by specific values

When retrieving data from a table, you often need to sort the results based on multiple values ​​in a specific order. In this case, the task is to extract records from a table based on specified keys and non-indexed fields and sort the results by multiple values ​​in a specific sequence.

To meet this requirement, a CASE expression can be used to achieve the desired ordering instead of using the traditional ORDER BY syntax. The syntax of CASE expression is as follows:

CASE
   WHEN condition1 THEN value1
   WHEN condition2 THEN value2
   ...
   ELSE valueN
END

In this example, the CASE expression is used to assign a specific order to each value in x_field. For example, the values ​​'f', 'p', 'i' and 'a' are assigned sequence numbers 1, 2, 3 and 4 respectively. Any other values ​​that do not match these specific values ​​will be assigned the default sequence number 5.

Here is an example of how a CASE expression can be added to a SQL query:

SELECT *
FROM table
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

By using a CASE expression, the results will be sorted according to the assigned sequence number, effectively sorting the values ​​'f', 'p', 'i' and 'a' in the specified sequence.

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