PostgreSQL查询中对pairs、change_id字段实现DISTINCT去重的正确方法

报错原因解释 #

你遇到的报错是PostgreSQL的语法规则限制：使用SELECT DISTINCT时，ORDER BY子句中用到的字段必须包含在SELECT的返回字段列表中。因为DISTINCT会先对返回的字段值组合去重，此时未被选中的a.created_at已经不存在于结果集中，数据库无法基于该字段完成排序。

另外你的需求是按pairs、a.change_id分组去重，且取每组a.created_at最大的记录，直接用普通DISTINCT无法满足该需求，推荐以下两种实现方案： #

方案1：使用PostgreSQL专属的DISTINCT ON（性能最优） #

DISTINCT ON是PostgreSQL特有的语法，可以直接按指定字段分组，取每组排序后的第一条记录，完全匹配你的需求：

SELECT DISTINCT ON (pairs, a.change_id) 
    pairs, a.change_id, user_size, user_mile, b.change_short_name
FROM order_data a
FULL OUTER JOIN changes b
ON a.change_id = b.change_id
-- 先按去重字段排序，再按created_at倒序，每组第一条就是最大created_at对应的记录
ORDER BY pairs, a.change_id, a.created_at DESC;

注意：如果你需要保留FULL OUTER JOIN中仅存在于b表、a表无匹配的行，这些行的pairs、a.change_id会为NULL，如果你不需要这些行，可以把FULL OUTER JOIN改成INNER JOIN。

方案2：使用窗口函数（兼容性更好，支持其他SQL数据库） #

如果需要兼容其他数据库（如MySQL、Oracle等），可以用ROW_NUMBER()窗口函数实现：

WITH ranked_data AS (
    SELECT 
        pairs, a.change_id, user_size, user_mile, b.change_short_name,
        ROW_NUMBER() OVER (
            PARTITION BY pairs, a.change_id 
            ORDER BY a.created_at DESC
        ) AS rn
    FROM order_data a
    FULL OUTER JOIN changes b
    ON a.change_id = b.change_id
)
SELECT pairs, change_id, user_size, user_mile, change_short_name
FROM ranked_data
WHERE rn = 1;

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