如何按PID分组，基于YEAR、ID最大值筛选KEY与PID的SQL查询

嘿，这个分组取最优行的需求很常见，用窗口函数就能完美解决，我给你拆解一下：

原始数据表 #

查询规则 #

针对每个PID，我们需要返回唯一的KEY和对应PID，遵循两个优先级：

• 第一优先级：选取YEAR最新（数值最大）的行
• 第二优先级：若同一PID下YEAR相同，选取ID最大的行

预期结果 #

实现SQL #

用ROW_NUMBER()窗口函数可以高效实现这个逻辑，代码如下：

SELECT KEY, PID
FROM (
    SELECT 
        KEY, 
        PID,
        -- 按PID分组，先按年份倒序，再按ID倒序分配行号
        ROW_NUMBER() OVER (
            PARTITION BY PID 
            ORDER BY YEAR DESC, ID DESC
        ) AS row_rank
    FROM your_table_name -- 替换成你的实际表名
) ranked_data
WHERE row_rank = 1;

逻辑说明 #

1. 分组：PARTITION BY PID把数据按PID拆分成独立的组，每个组内单独处理
2. 排序：ORDER BY YEAR DESC, ID DESC确保每个组内先排最新年份，年份相同时排最大ID
3. 标记最优行：ROW_NUMBER()给每个组内的行按排序规则分配序号，符合要求的第一行序号为1
4. 筛选结果：外层查询只保留序号为1的行，就是每个PID对应的目标行

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