如何使PostgreSQL查询结果按WHERE子句传入项精确排序？有无FIELD()等效函数？

假设你执行以下SQL：

SELECT id, name FROM my_table
WHERE id in (1,3,5,2,4,6)
ORDER BY id

得到的结果是按id升序排列：

id | name
---------
1  |  a
2  |  b
3  |  c
4  |  d
5  |  e
6  |  f

但你期望结果按照IN子句中(1,3,5,2,4,6)的精确顺序返回：

id | name
---------
1  |  a
3  |  c
5  |  e
2  |  b
4  |  d
6  |  f

MySQL里有FIELD()函数可以实现这个需求，PostgreSQL没有直接对应的函数，但可以通过以下几种方式实现：

• 方法一：使用array_position()函数（推荐）
  这是PostgreSQL中最简洁的实现方式，利用数组的位置来排序：

  SELECT id, name FROM my_table
  WHERE id IN (1,3,5,2,4,6)
  ORDER BY array_position(ARRAY[1,3,5,2,4,6], id);
  

  原理：先创建一个包含目标顺序的数组，array_position()会返回每个id在数组中的位置，按这个位置排序就能得到你要的顺序。

• 方法二：使用CASE语句
  如果IN列表较短，可以直接用CASE给每个id指定排序优先级：

  SELECT id, name FROM my_table
  WHERE id IN (1,3,5,2,4,6)
  ORDER BY CASE id
      WHEN 1 THEN 1
      WHEN 3 THEN 2
      WHEN 5 THEN 3
      WHEN 2 THEN 4
      WHEN 4 THEN 5
      WHEN 6 THEN 6
  END;
  

• 方法三：使用WITH ORDINALITY关联顺序（适合动态长列表）
  如果你的id列表是动态生成的（比如从应用程序传入），可以用临时表关联顺序：

  WITH ordered_ids AS (
      SELECT unnest(ARRAY[1,3,5,2,4,6]) AS id, ordinality AS sort_order
  )
  SELECT t.id, t.name
  FROM my_table t
  JOIN ordered_ids oi ON t.id = oi.id
  ORDER BY oi.sort_order;
  

  原理：unnest()把数组拆成单行数据，WITH ORDINALITY会给每一行加上原始的顺序编号，通过JOIN关联原表后，按编号排序即可。

内容的提问来源于stack exchange，提问作者Chris Kao