如何编写PostgreSQL查询获取分组子行的最大版本号行

问题原因 #

PostgreSQL严格遵循SQL标准，要求SELECT列表中的非聚合列必须出现在GROUP BY子句中，或者被聚合函数处理。而MySQL在关闭ONLY_FULL_GROUP_BY模式时允许非标准写法，会随机返回非聚合列的值，这就是你的语句在MySQL能运行但PostgreSQL报错的核心原因。

解决方案 #

方法1：窗口函数（通用方案，适配多数数据库） #

利用ROW_NUMBER()窗口函数对每个filekey分组内的行按version_num降序排序，筛选出每组的第一行：

SELECT t.*
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY filekey ORDER BY version_num DESC) AS row_rank
    FROM testfiles
) t
WHERE t.row_rank = 1;

方法2：PostgreSQL特有DISTINCT ON（简洁高效） #

PostgreSQL的DISTINCT ON语法可直接保留每个分组的第一行，配合ORDER BY指定分组内的排序规则：

SELECT DISTINCT ON (filekey) *
FROM testfiles
ORDER BY filekey, version_num DESC;

注意：ORDER BY必须以DISTINCT ON指定的列开头，确保分组逻辑正确。

方法3：子查询关联（传统方案） #

先通过子查询获取每个filekey对应的最大version_num，再关联原表提取完整行数据：

SELECT tf.*
FROM testfiles tf
INNER JOIN (
    SELECT filekey, MAX(version_num) AS max_version
    FROM testfiles
    GROUP BY filekey
) max_versions 
    ON tf.filekey = max_versions.filekey 
    AND tf.version_num = max_versions.max_version;

验证结果 #

以上三种方法均可得到预期输出：

-   (5, 'myDoc.txt', 15, '1d', 16, '08/08/2022', 'Jonathan')
-   (10, 'myDoc2.txt', 20, '1i', 16, '08/09/2022', 'Jonathan')

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