按IN值列表排序

I have a simple SQL query in PostgreSQL 8.3 that grabs a bunch of comments. I provide a sorted list of values to the IN construct in the WHERE clause:

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

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.

它以任意的顺序返回注释我的id是1 2 3 4。

I want the resulting rows sorted like the list in the IN construct: (1,3,2,4).
How to achieve that?

我希望结果行的排序方式与in结构中的列表类似:(1、3、2、4)。如何实现呢?

16 个解决方案

#1

You can do it quite easily with (introduced in PostgreSQL 8.2) VALUES (), ().

使用(PostgreSQL 8.2中引入的)值()、()可以很容易地完成。

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

#2

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')

#3

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

#4

In Postgres 9.4 or 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.

使用带有序数的新，@a_horse已经提到。
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 constructor may be easier to implement with some clients.

与数组构造函数相关联的字符串字面量可能更容易实现一些客户端。

Detailed explanation:

详细解释:

PostgreSQL unnest() with element number
带有元素号的PostgreSQL unnest()

#5

Another way to do it in Postgres would be to use the idx function.

在Postgres中执行此操作的另一种方法是使用idx函数。

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

Don't forget to create the idx function first, as described here: http://wiki.postgresql.org/wiki/Array_Index

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

#6

With Postgres 9.4 this 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

Removing the need to manually assign/maintain a position to each value.

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

With Postgres 9.6 this 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);

#7

In Postgresql:

在Postgresql:

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

#8

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?

然而，这似乎相当冗长，并且在大型数据集上可能存在性能问题。有人能对这些问题发表评论吗?

#9

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”表，它定义了IDs的映射(有效地执行您对您自己的问题的响应)，然后您可以将其用作您选择的另一列，然后您可以进行排序。

In that way, you explicitly describe the ordering you desire in the database, where it should be.

通过这种方式，您可以显式地描述数据库中所需的排序，以及它的位置。

#10

sans SEQUENCE, works only on 8.4:

无序列，仅在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

#11

SELECT * FROM "comments" JOIN (
  SELECT 1 as "id",1 as "order" UNION ALL 
  SELECT 3,2 UNION ALL SELECT 2,3 UNION ALL SELECT 4,4
) j ON "comments"."id" = j."id" ORDER BY j.ORDER

or if you prefer evil over good:

如果你喜欢邪恶胜过善良:

SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4))
ORDER BY POSITION(','+"comments"."id"+',' IN ',1,3,2,4,')

#12

I agree with all other posters that say "don't do that" or "SQL isn't good at that". If you want to sort by some facet of comments then add another integer column to one of your tables to hold your sort criteria and sort by that value. eg "ORDER BY comments.sort DESC " If you want to sort these in a different order every time then... SQL won't be for you in this case.

我同意所有说“不要那样做”或“SQL不擅长那样”的海报。如果您希望按注释的某些方面进行排序，那么请向其中一个表中添加另一个整数列，以保存排序标准并按该值进行排序。如“秩序的评论。排序DESC如果你想每次都以不同的顺序排序的话……在这种情况下，SQL不适合您。

#13

And here's another solution that works and uses a constant table (http://www.postgresql.org/docs/8.3/interactive/sql-values.html):

这是另一个有效且使用常量表的解决方案(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

But again I'm not sure that this is performant.

但我不确定这是否是表演。

I've got a bunch of answers now. Can I get some voting and comments so I know which is the winner!

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

Thanks All :-)

谢谢:-)

#14

create sequence serial start 1;

select * from comments c
join (select unnest(ARRAY[1,3,2,4]) as id, nextval('serial') as id_sorter) x
on x.id = c.id
order by x.id_sorter;

drop sequence serial;

[EDIT]

(编辑)

unnest is not yet built-in in 8.3, but you can create one yourself(the beauty of any*):

unnest还没有内置在8.3中，但你可以自己创建一个(任何*的美):

create function unnest(anyarray) returns setof anyelement
language sql as
$$
    select $1[i] from generate_series(array_lower($1,1),array_upper($1,1)) i;
$$;

that function can work in any type:

该函数可以在任何类型中工作:

select unnest(array['John','Paul','George','Ringo']) as beatle
select unnest(array[1,3,2,4]) as id

#15

Slight improvement over the version that uses a sequence I think:

对使用序列的版本稍有改进:

CREATE OR REPLACE FUNCTION in_sort(anyarray, out id anyelement, out ordinal int)
LANGUAGE SQL AS
$$
    SELECT $1[i], i FROM generate_series(array_lower($1,1),array_upper($1,1)) i;
$$;

SELECT 
    * 
FROM 
    comments c
    INNER JOIN (SELECT * FROM in_sort(ARRAY[1,3,2,4])) AS in_sort
        USING (id)
ORDER BY in_sort.ordinal;

#16

select * from comments where comments.id in 
(select unnest(ids) from bbs where id=19795) 
order by array_position((select ids from bbs where id=19795),comments.id)

here, [bbs] is the main table that has a field called ids, and, ids is the array that store the comments.id .

在这里，[bbs]是具有名为ids的字段的主表，而ids是存储注释的数组。id。

passed in postgresql 9.6

通过在postgresql 9.6

#1