我在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)。 如何实现这一目标?
当前回答
无序列,只适用于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
其他回答
你可以很容易地使用(在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
在Postgresql:
select *
from comments
where id in (1,3,2,4)
order by position(id::text in '1,3,2,4')
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
或者如果你喜欢恶而不是善:
SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4))
ORDER BY POSITION(','+"comments"."id"+',' IN ',1,3,2,4,')
只是因为它很难找到,它必须被传播:在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 (
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);