sql ORDER BY 按特定顺序排列多个值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6332043/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql ORDER BY multiple values in specific order?
提问by Phill Pafford
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.
好的，我有一个带有索引键和非索引字段的表。我需要找到具有特定值的所有记录并返回该行。我想知道我是否可以按多个值排序。
Example:
例子：
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: would like the results to be ordered like this, where ORDER BY x_field = 'f', 'p', 'i', 'a'
伪：希望结果像这样排序， 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'
So the results would be:
所以结果将是：
id x_field
-- -----
127 f
133 p
134 p
135 i
123 a
124 a
125 a
129 a
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?
语法是有效的，但是当我执行查询时，它永远不会返回任何结果，即使我将其限制为 1 条记录。有没有其他方法可以解决这个问题？
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.
将 x_field 视为测试结果，我需要验证符合条件的所有记录。我想按失败的值、通过的值对测试结果进行排序。所以我可以先验证失败的值，然后使用 ORDER BY 验证传递的值。
What I can't do:
我不能做什么：
- GROUP BY, as I need to return the specific record values
- WHERE x_field IN('f', 'p', 'i', 'a'), I need all the values as I'm trying to use one query for several validation tests. And x_field values are not in DESC/ASC order
- 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!
写完这个问题后，我开始认为我需要重新思考这个问题，大声笑！
回答by gbn
...
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
回答by peufeu
You can use a LEFT JOIN with a "VALUES ('f',1),('p',2),('a',3),('i',4)" and use the second column in your order-by expression. Postgres will use a Hash Join which will be much faster than a huge CASE if you have a lot of values. And it is easier to autogenerate.
您可以使用带有 "VALUES ('f',1),('p',2),('a',3),('i',4)" 的 LEFT JOIN 并使用订单中的第二列- 通过表达。Postgres 将使用哈希联接，如果您有很多值，这将比巨大的 CASE 快得多。而且更容易自动生成。
If this ordering information is fixed, then it should have its own table.
如果这个排序信息是固定的，那么它应该有自己的表。
回答by Marc B
Try:
尝试：
ORDER BY x_field='F', x_field='P', x_field='A', x_field='I'
You were on the right track, but by putting x_field only on the F value, the other 3 were treated as constants and not compared against anything in the dataset.
您走在正确的轨道上，但是通过仅将 x_field 放在 F 值上，其他 3 个被视为常量，而不与数据集中的任何内容进行比较。
回答by Guffa
Use a caseswitch to translate the codes into numbers that can be sorted:
使用case开关将代码转换为可排序的数字：
ORDER BY
case x_field
when 'f' then 1
when 'p' then 2
when 'i' then 3
when 'a' then 4
else 5
end
回答by rept
I found a much cleaner solution for this:
我为此找到了一个更清洁的解决方案：
ORDER BY array_position(ARRAY['f', 'p', 'i', 'a']::varchar[], x_field)
Note: array_position needs Postgres v9.5 or higher.
注意：array_position 需要 Postgres v9.5 或更高版本。
回答by Andrew Lazarus
The CASEand ORDER BYsuggestions should all work, but I'm going to suggest a horse of a different color. Assuming that there are only a reasonable number of values for x_fieldand you already know what they are, create an enumerated typewith F, P, A, and I as the values (plus whatever other possible values apply). Enums will sort in the order implied by their CREATEstatement. Also, you can use meaninful value names—your real application probably does and you have just masked them for confidentiality—without wasted space, since only the ordinal position is stored.
该CASE和ORDER BY建议都应该工作，但是我要提出一个不同颜色的马。假设只有合理数量的值x_field并且您已经知道它们是什么，创建一个枚举类型，将 F、P、A 和 I 作为值（加上任何其他可能适用的值）。枚举将按照它们的CREATE语句隐含的顺序进行排序。此外，您可以使用有意义的值名称——您的实际应用程序可能会这样做，并且您只是为了保密而将它们屏蔽了——而不会浪费空间，因为只存储序数位置。
回答by JIYAUL MUSTAPHA
You can order by a selected column or other expressions.
您可以按选定的列或其他表达式进行排序。
Here an example, how to order by the result of a case-statement:
这是一个示例，如何按 case 语句的结果排序：
SELECT col1
, col2
FROM tbl_Bill
WHERE col1 = 0
ORDER BY -- order by case-statement
CASE WHEN tbl_Bill.IsGen = 0 THEN 0
WHEN tbl_Bill.IsGen = 1 THEN 1
ELSE 2 END
The result will be a List starting with "IsGen = 0" rows, followed by "IsGen = 1" rows and all other rows a the end.
结果将是一个以“IsGen = 0”行开头的列表，然后是“IsGen = 1”行和所有其他行的结尾。
You could add more order-parameters at the end:
您可以在最后添加更多订单参数：
SELECT col1
, col2
FROM tbl_Bill
WHERE col1 = 0
ORDER BY -- order by case-statement
CASE WHEN tbl_Bill.IsGen = 0 THEN 0
WHEN tbl_Bill.IsGen = 1 THEN 1
ELSE 2 END,
col1,
col2
回答by Akitha_MJ
For someone who is new to ORDER BYwith CASEthis may be useful
对于不熟悉ORDER BYwith CASE 的人，这可能很有用
ORDER BY
CASE WHEN GRADE = 'A' THEN 0
WHEN GRADE = 'B' THEN 1
ELSE 2 END
回答by Mukesh Kulal
you can use position(text in text) in order by for ordering the sequence
您可以按顺序使用位置（文本中的文本）来对序列进行排序
