如何基于report_type_code提取最大值行？SQL语句优化求助

需要为每个report_type_code提取对应object_version_number最大值的行，尝试的SQL输出存在重复的report_type_code条目，不符合预期，需调整语句实现需求。

原SQL语句 #

SELECT gfrb.report_type_code,
       gfrb.report_folder,
       Max (gfrb.object_version_number) "MAX"
FROM   gl_frc_reports_b gfrb
WHERE  gfrb.report_path LIKE '%REP514%'
GROUP  BY gfrb.report_type_code,
          gfrb.report_folder 

当前输出 #

预期输出 #

解决方案 #

方法1：窗口函数法（推荐） #

利用ROW_NUMBER()窗口函数，按report_type_code分组后，对每组内的object_version_number降序编号，取编号为1的行（即最大值行）：

SELECT report_type_code, report_folder, object_version_number AS "MAX"
FROM (
    SELECT 
        gfrb.report_type_code,
        gfrb.report_folder,
        gfrb.object_version_number,
        -- 按report_type_code分组，每组内按版本号降序生成行号
        ROW_NUMBER() OVER (PARTITION BY gfrb.report_type_code ORDER BY gfrb.object_version_number DESC) AS rn
    FROM gl_frc_reports_b gfrb
    WHERE gfrb.report_path LIKE '%REP514%'
) t
-- 只保留每组的第一行（最大值行）
WHERE rn = 1;

方法2：子查询关联法 #

先通过子查询获取每个report_type_code的最大版本号，再关联原表匹配对应行：

SELECT gfrb.report_type_code, gfrb.report_folder, gfrb.object_version_number AS "MAX"
FROM gl_frc_reports_b gfrb
JOIN (
    -- 先得到每个report_type_code的最大版本号
    SELECT report_type_code, MAX(object_version_number) AS max_version
    FROM gl_frc_reports_b
    WHERE report_path LIKE '%REP514%'
    GROUP BY report_type_code
) t ON gfrb.report_type_code = t.report_type_code 
   AND gfrb.object_version_number = t.max_version
WHERE gfrb.report_path LIKE '%REP514%';

原SQL问题说明 #

原SQL将report_folder也加入了GROUP BY子句，导致同一个report_type_code下不同的report_folder会被拆分为独立分组，因此出现了重复的report_type_code条目。

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