我在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)。 如何实现这一目标?
我在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)。 如何实现这一目标?
当前回答
你可以很容易地使用(在PostgreSQL 8.2中引入)VALUES(),()。
语法是这样的:
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
其他回答
我认为这样比较好:
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
只是因为它很难找到,它必须被传播:在mySQL中,这可以做得更简单,但我不知道它是否适用于其他SQL。
SELECT * FROM `comments`
WHERE `comments`.`id` IN ('12','5','3','17')
ORDER BY FIELD(`comments`.`id`,'12','5','3','17')
你可以很容易地使用(在PostgreSQL 8.2中引入)VALUES(),()。
语法是这样的:
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
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
在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()与元素号