Multi-value sort database records in a specific order

]

Suppose you have a table with an index key and a non-index field x_field. You need to find all records with specific values ​​and return them and sort the results according to multiple values ​​in a specific order.

For example, if you have the following table:

idx_field123a124aa]a125a126b127128bb &&]129a130x131x132b133p

	124
f

134

ppi]] and you want the results to be sorted in the following order, where the order is x_field = 'f', 'p', 'i', 'a'idx_field127f133p134p135i123a

	135
	]
:

124

SELECT *
FROM table
WHERE id NOT IN (126)
ORDER BY x_field 'f', 'p', 'i', 'a'

a

125a

...
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

a

You initially tried using the following query:

SELECT * FROM table WHERE id NOT IN (126) ORDER BY x_field 'f', 'p', 'i', 'a'

However, this query returns no results.

The solution to this problem is to use the CASE

statement to assign a numeric value to each

x_field value in the desired order: ]

... 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 -- For fallback values ​​not in the IN clause (for example: x_field = 'b') END, id

This query assigns value 1 to the

x_field

value equal to 'f', value 2 to the value equal to 'p', value 3 to the value equal to 'i', and value 4 to the value equal to 'a'. For values ​​not in the IN clause (for example 'b'), a fallback value of 5 is assigned. Then sort the results in ascending order based on this value and the idx_field values ​​are not in descending/ascending order.

field.	This method ensures that the results are sorted in the desired order, even if the