postgresql 如何按特定顺序排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8713266/
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
How do I sort by a specific order
提问by Lilleman
Table users:
表用户：
id | firstname | lastname
---+-----------+---------
1 | John | Smith
2 | Adam | Tensta
3 | Anna | Johansson
I want to select these in the order of ID 2, 3, 1. ONLY specified by the id-field. Is this possible?
我想按照 ID 2、3、1 的顺序选择这些。仅由 id 字段指定。这可能吗？
I'm thinking something like SELECT * FROM users ORDER BY id ORDER(2,3,1)
我在想像 SELECT * FROM users ORDER BY id ORDER(2,3,1)
Can this be done, and in that case how?
这可以做到吗，在那种情况下怎么做？
回答by
Should work with a CASE in the order by:
应该按以下顺序使用 CASE：
SELECT *
FROM users
ORDER BY case id when 2 then 1
when 3 then 2
when 1 then 3
end
回答by Erwin Brandstetter
Generic solution for Postgres 9.4 or later
Postgres 9.4 或更高版本的通用解决方案
For any number of values. Just pass an array of matching type with your preferred sort order:
对于任意数量的值。只需使用您喜欢的排序顺序传递匹配类型的数组：
SELECT u.*
FROM users u
LEFT JOIN unnest('{2,3,1}'::int[]) WITH ORDINALITY o(id, ord) USING (id)
ORDER BY o.ord;
This sorts rows without match last because ordis NULL then and that sorts last in ascending order.
这对没有匹配的行进行最后排序，因为它ord是 NULL 然后按升序排序最后。
Similar solutions possible for older versions without ORDINALITY.
对于没有ORDINALITY.
Related:
有关的：
Original answer for the given simple case
给定简单案例的原始答案
SELECT * FROM users ORDER BY (id+1)%3
%is the modulo operator.
%是模运算符。
