SQL ORDER BY IN 值列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/866465/
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
ORDER BY the IN value list
提问by nutcracker
I have a simple SQL query in PostgreSQL 8.3 that grabs a bunch of comments. I provide a sortedlist of values to the INconstruct in the WHEREclause:
我在 PostgreSQL 8.3 中有一个简单的 SQL 查询，它抓取了一堆注释。我为子句中的构造提供了一个排序的值列表：INWHERE
SELECT * FROM comments WHERE (comments.id IN (1,3,2,4));
This returns comments in an arbitrary order which in my happens to be ids like 1,2,3,4.
这会以任意顺序返回评论，而我的评论恰好是1,2,3,4.
I want the resulting rows sorted like the list in the INconstruct: (1,3,2,4).
How to achieve that?
我想整理喜欢在列表中的结果行IN的结构：(1,3,2,4)。
如何做到这一点？
采纳答案by nutcracker
You can do it quite easily with (introduced in PostgreSQL 8.2) VALUES (), ().
您可以使用（在 PostgreSQL 8.2 中引入）VALUES (), () 轻松完成。
Syntax will be like this:
语法将是这样的：
select c.*
from comments c
join (
values
(1,1),
(3,2),
(2,3),
(4,4)
) as x (id, ordering) on c.id = x.id
order by x.ordering
回答by das oe
Just because it is so difficult to find and it has to be spread: in mySQL this can be done much simpler, but I don't know if it works in other SQL.
只是因为它很难找到并且必须传播：在mySQL中这可以做得更简单，但我不知道它是否适用于其他SQL。
SELECT * FROM `comments`
WHERE `comments`.`id` IN ('12','5','3','17')
ORDER BY FIELD(`comments`.`id`,'12','5','3','17')
回答by Erwin Brandstetter
In Postgres 9.4or later, this is probably simplest and fastest:
在 Postgres 9.4或更高版本中，这可能是最简单和最快的：
SELECT c.*
FROM comments c
JOIN unnest('{1,3,2,4}'::int[]) WITH ORDINALITY t(id, ord) USING (id)
ORDER BY t.ord;
Using the new
WITH ORDINALITY, that @a_horse already mentioned.We don't need a subquery, we can use the set-returning function like a table.
A string literal to hand in the array instead of an ARRAY constructormay be easier to implement with some clients.
我们不需要子查询，我们可以像表一样使用 set-returning 函数。
对于某些客户端，要在数组中提交的字符串文字而不是ARRAY 构造函数可能更容易实现。
Detailed explanation:
详细解释：
回答by vantrung -cuncon
I think this way is better :
我认为这种方式更好：
SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4))
ORDER BY id=1 DESC, id=3 DESC, id=2 DESC, id=4 DESC
回答by a_horse_with_no_name
With Postgres 9.4this can be done a bit shorter:
使用Postgres 9.4，这可以缩短一点：
select c.*
from comments c
join (
select *
from unnest(array[43,47,42]) with ordinality
) as x (id, ordering) on c.id = x.id
order by x.ordering;
Or a bit more compact without a derived table:
或者更紧凑一点没有派生表：
select c.*
from comments c
join unnest(array[43,47,42]) with ordinality as x (id, ordering)
on c.id = x.id
order by x.ordering
Removing the need to manually assign/maintain a position to each value.
无需手动分配/维护每个值的位置。
With Postgres 9.6this can be done using array_position():
使用Postgres 9.6，这可以使用array_position()：
with x (id_list) as (
values (array[42,48,43])
)
select c.*
from comments c, x
where id = any (x.id_list)
order by array_position(x.id_list, c.id);
The CTE is used so that the list of values only needs to be specified once. If that is not important this can also be written as:
使用 CTE 以便只需要指定一次值列表。如果这不重要，这也可以写成：
select c.*
from comments c
where id in (42,48,43)
order by array_position(array[42,48,43], c.id);
回答by Carl Mercier
Another way to do it in Postgres would be to use the idxfunction.
在 Postgres 中执行此操作的另一种方法是使用该idx函数。
SELECT *
FROM comments
ORDER BY idx(array[1,3,2,4], comments.id)
Don't forget to create the idxfunction first, as described here: http://wiki.postgresql.org/wiki/Array_Index
不要忘记首先创建idx函数，如下所述：http: //wiki.postgresql.org/wiki/Array_Index
回答by Clodoaldo Neto
In Postgresql:
在 PostgreSQL 中：
select *
from comments
where id in (1,3,2,4)
order by position(id::text in '1,3,2,4')
回答by nutcracker
On researching this some more I found this solution:
在对此进行更多研究时，我找到了这个解决方案：
SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4))
ORDER BY CASE "comments"."id"
WHEN 1 THEN 1
WHEN 3 THEN 2
WHEN 2 THEN 3
WHEN 4 THEN 4
END
However this seems rather verbose and might have performance issues with large datasets. Can anyone comment on these issues?
然而，这似乎相当冗长，并且可能在大型数据集上存在性能问题。任何人都可以评论这些问题吗？
回答by Paul Sonier
To do this, I think you should probably have an additional "ORDER" table which defines the mapping of IDs to order (effectively doing what your response to your own question said), which you can then use as an additional column on your select which you can then sort on.
为此，我认为您可能应该有一个额外的“ORDER”表，它定义了 ID 到订单的映射（有效地按照您对自己问题的回答进行了操作），然后您可以将其用作您选择的附加列然后你可以排序。
In that way, you explicitly describe the ordering you desire in the database, where it should be.
这样一来，你明确地描述你在数据库中，它应该是想要的顺序。
回答by Michael Buen
sans SEQUENCE, works only on 8.4:
SANS序列，仅适用于8.4：
select * from comments c
join
(
select id, row_number() over() as id_sorter
from (select unnest(ARRAY[1,3,2,4]) as id) as y
) x on x.id = c.id
order by x.id_sorter
