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

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

在Postgres中另一种方法是使用idx函数。

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

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

让我们对已经说过的内容有一个直观的印象。例如，你有一个带有一些任务的表:

SELECT a.id,a.status,a.description FROM minicloud_tasks as a ORDER BY random();

 id |   status   |   description    
----+------------+------------------
  4 | processing | work on postgres
  6 | deleted    | need some rest
  3 | pending    | garden party
  5 | completed  | work on html

并且您希望按状态对任务列表进行排序。 status是一个字符串值列表:

(processing, pending,  completed, deleted)

诀窍是给每个状态值一个整数，并以数字顺序排列列表:

SELECT a.id,a.status,a.description FROM minicloud_tasks AS a
  JOIN (
    VALUES ('processing', 1), ('pending', 2), ('completed', 3), ('deleted', 4)
  ) AS b (status, id) ON (a.status = b.status)
  ORDER BY b.id ASC;

这就导致:

 id |   status   |   description    
----+------------+------------------
  4 | processing | work on postgres
  3 | pending    | garden party
  5 | completed  | work on html
  6 | deleted    | need some rest

信贷@user80168