sql ORDER BY 按特定顺序排列多个值? [英] sql ORDER BY multiple values in specific order?

问题描述

好的，我有一个带有索引键和非索引字段的表.我需要找到具有特定值的所有记录并返回该行.我想知道我是否可以按多个值排序.

Ok I have a table with a indexed key and a non indexed field. I need to find all records with a certain value and return the row. I would like to know if I can order by multiple values.

示例:

id     x_field
--     -----
123    a
124    a
125    a
126    b
127    f
128    b
129    a
130    x
131    x
132    b
133    p
134    p
135    i

伪:希望结果像这样排序，where ORDER BY x_field = 'f', 'p', 'i', 'a'

pseudo: would like the results to be ordered like this, where ORDER BY x_field = 'f', 'p', 'i', 'a'

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

所以结果是:

id     x_field
--     -----
127    f
133    p
134    p
135    i
123    a
124    a
125    a
129    a

语法是有效的，但是当我执行查询时，它永远不会返回任何结果，即使我将其限制为 1 条记录.还有其他方法可以解决这个问题吗?

The syntax is valid but when I execute the query it never returns any results, even if I limit it to 1 record. Is there another way to go about this?

将 x_field 视为测试结果，我需要验证符合条件的所有记录.我想按失败的值、通过的值对测试结果进行排序.所以我可以先验证失败的值，然后使用 ORDER BY 验证传递的值.

Think of the x_field as test results and I need to validate all the records that fall in the condition. I wanted to order the test results by failed values, passed values. So I could validate the failed values first and then the passed values using the ORDER BY.

我不能做什么:

• GROUP BY，因为我需要返回特定的记录值
• WHERE x_field IN('f', 'p', 'i', 'a')，我需要所有值，因为我正在尝试使用一个查询进行多个验证测试.并且 x_field 值不在 DESC/ASC 顺序中

写完这个问题后，我开始觉得我需要重新思考这个问题，哈哈！

After writing this question I'm starting to think that I need to rethink this, LOL!

推荐答案

...
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 --needed only is no IN clause above. eg when = 'b'
   END, id

这篇关于sql ORDER BY 按特定顺序排列多个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！