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
pseudo:希望结果按如下顺序排序,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,因为我需要返回特定的记录值
- 在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屋!