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

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

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

谢谢大家:-)

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

在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()与元素号

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)

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

在postgresql 9.6中通过

在Postgresql:

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

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