如何在PostgreSQL的SELECT语句中按IN子句值顺序排序

你需要查询column_xyz值在指定列表中的记录，并且让结果按列表里的顺序返回，但PostgreSQL不支持MySQL的FIELD()函数，以下是几种可行的解决方法：

方法一：使用array_position函数（PostgreSQL 9.5+） #

这是最简洁的方式，利用数组元素的位置来排序：

SELECT *
FROM table_name
WHERE column_xyz IN ('124', '225', '111')
ORDER BY array_position(ARRAY['124', '225', '111'], column_xyz);

array_position会返回column_xyz的值在指定数组中的位置，按这个位置排序就会和IN子句里的顺序一致。

方法二：使用CASE表达式（兼容低版本PG） #

如果你的PostgreSQL版本低于9.5，用CASE表达式手动指定排序优先级：

SELECT *
FROM table_name
WHERE column_xyz IN ('124', '225', '111')
ORDER BY 
    CASE column_xyz
        WHEN '124' THEN 1
        WHEN '225' THEN 2
        WHEN '111' THEN 3
    END;

给每个需要匹配的值分配一个排序序号，序号越小越靠前，完全对应你想要的顺序。

方法三：使用unnest+WITH ORDINALITY（适合动态列表） #

如果IN子句的列表是动态生成或者比较长，可以用临时表关联的方式：

WITH ordered_values AS (
    SELECT value, ordinality
    FROM unnest(ARRAY['124', '225', '111']) WITH ORDINALITY AS t(value, ordinality)
)
SELECT t.*
FROM table_name t
JOIN ordered_values ov ON t.column_xyz = ov.value
ORDER BY ov.ordinality;

unnest把数组拆成单行记录，WITH ORDINALITY会给每条记录加上顺序号，关联原表后按顺序号排序即可。

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