按IN值列表排序

我在PostgreSQL 8.3中有一个简单的SQL查询，它抓取了一堆注释。我在WHERE子句中为IN构造提供了一个排序的值列表:

SELECT * FROM comments WHERE (comments.id IN (1,3,2,4));

这将以任意顺序返回注释，在my中恰好是id，如1,2,3,4。

我希望结果行像in结构中的列表一样排序:(1,3,2,4)。如何实现这一目标?

当前回答

在Postgres中另一种方法是使用idx函数。

SELECT *
FROM comments
ORDER BY idx(array[1,3,2,4], comments.id)

不要忘记首先创建idx函数，如下所述:http://wiki.postgresql.org/wiki/Array_Index

其他回答

在Postgresql:

select *
from comments
where id in (1,3,2,4)
order by position(id::text in '1,3,2,4')

我同意其他所有说“不要那样做”或“SQL不擅长那样做”的帖子。如果您想按注释的某个方面排序，那么向其中一个表添加另一个整数列以保存排序条件并按该值排序。按注释排序。“如果你想每次都按不同的顺序排序，那么……在这种情况下，SQL不适合您。

在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;

或者在没有派生表的情况下更加紧凑:

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

无需手动为每个值分配/维护位置。

在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);

使用CTE时，值列表只需要指定一次。如果这并不重要，也可以写成:

select c.*
from comments c
where id in (42,48,43)
order by array_position(array[42,48,43], c.id);

这里有另一个解决方案，它使用了一个常量表(http://www.postgresql.org/docs/8.3/interactive/sql-values.html):)

SELECT * FROM comments AS c,
(VALUES (1,1),(3,2),(2,3),(4,4) ) AS t (ord_id,ord)
WHERE (c.id IN (1,3,2,4)) AND (c.id = t.ord_id)
ORDER BY ord

但我不确定这是表现性的。

我现在有一堆答案了。我可以得到一些投票和评论，这样我就知道哪一个是赢家!

谢谢大家:-)

在Postgres中另一种方法是使用idx函数。

SELECT *
FROM comments
ORDER BY idx(array[1,3,2,4], comments.id)

不要忘记首先创建idx函数，如下所述:http://wiki.postgresql.org/wiki/Array_Index

推荐文章