PostgreSQL按ID分组取两列最大日期时子查询返回多行报错如何解决

原SQL错误原因 #

• 错误1：第二个子查询使用GROUP BY "ID"，会返回N行结果（N为不同ID的数量），而=运算符要求右侧只能返回单一值，因此触发「more than one row returned by a subquery used as an expression」报错。
• 错误2：第一个子查询取的是全表最大的Upload_Date，不符合「每个ID下取自身最新日期」的需求，逻辑不匹配。

正确实现方案 #

PostgreSQL下推荐两种实现方式：

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

SELECT DISTINCT ON ("ID") *
FROM public.extract
ORDER BY "ID", "Upload_Date" DESC, "date_modification" DESC;

逻辑说明：DISTINCT ON ("ID")会保留每个ID分组的第一行记录，排序规则设置为先按ID分组，组内先按Upload_Date倒序、再按date_modification倒序，取到的第一行就是每个ID下两个日期最新的记录。

方案2：使用标准窗口函数（兼容所有SQL方言） #

WITH ranked_data AS (
    SELECT *,
           RANK() OVER (
               PARTITION BY "ID" 
               ORDER BY "Upload_Date" DESC, "date_modification" DESC
           ) AS rk
    FROM public.extract
)
SELECT * EXCLUDE (rk) FROM ranked_data WHERE rk = 1;

逻辑说明：先通过PARTITION BY "ID"按ID分组，组内对两个日期倒序排名，筛选排名为1的记录即为所需结果。如果同一个ID下存在多条完全相同的最大日期记录，RANK()会全部返回，若只需返回一条可将RANK()替换为ROW_NUMBER()，自行补充额外排序规则即可。

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