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

WITH ORDINALITY was introduced with in Postgres 9.4. No need for a subquery, we can use the set-returning function like a table directly. (A.k.a. "table-function".) A string literal to hand in the array instead of an ARRAY constructor may be easier to implement with some clients. For convenience (optionally), copy the column name we are joining to ("id" in the example), so we can join with a short USING clause to only get a single instance of the join column in the result. Works with any input type. If your key column is of type text, provide something like '{foo,bar,baz}'::text[].

详细解释:

PostgreSQL unnest()与元素号

其他回答

在Postgresql:

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

这里有另一个解决方案,它使用了一个常量表(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

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

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

谢谢大家:-)

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;

(编辑)

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

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

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

只是因为它很难找到,它必须被传播:在mySQL中,这可以做得更简单,但我不知道它是否适用于其他SQL。

SELECT * FROM `comments`
WHERE `comments`.`id` IN ('12','5','3','17')
ORDER BY FIELD(`comments`.`id`,'12','5','3','17')

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

WITH ORDINALITY was introduced with in Postgres 9.4. No need for a subquery, we can use the set-returning function like a table directly. (A.k.a. "table-function".) A string literal to hand in the array instead of an ARRAY constructor may be easier to implement with some clients. For convenience (optionally), copy the column name we are joining to ("id" in the example), so we can join with a short USING clause to only get a single instance of the join column in the result. Works with any input type. If your key column is of type text, provide something like '{foo,bar,baz}'::text[].

详细解释:

PostgreSQL unnest()与元素号