按类型与日期分组获取对应最大Report_ID的SQL实现方法

需要针对数据表中每一组 Types（类型）、Date（日期） 的组合，筛选保留对应分组内 Report_ID（报告ID）数值最高的记录。实际数据表包含全量多日期数据，当前问题示例仅涉及2021年1月1日的测试数据场景。

现有待优化SQL #

SELECT a.*
FROM Table1 a
JOIN (
    SELECT Date, MAX(Report_ID) as maxID
    FROM Table1
    GROUP BY Date) b 
ON a.Report_ID = b.maxID
WHERE a.Date = '2021-01-01'
ORDER BY Date desc

现有方案存在的问题 #

• 分组逻辑缺失：子查询仅按 Date 维度分组，完全没有把 Types 纳入分组条件，会跨类型取全局最大的 Report_ID，直接导致类型维度的数据匹配错误
• 关联条件不全：仅靠 Report_ID 做关联，没有匹配 Date、Types 维度的等值校验，在多日期、多类型的大表场景下极易出现跨组数据错配
• 过滤条件写死：硬编码了 WHERE a.Date = '2021-01-01' 的单日期过滤，无法满足全表多日期的查询需求

可正确实现需求的SQL方案 #

方案1：修正关联子查询逻辑（兼容所有SQL版本） #

SELECT a.*
FROM Table1 a
INNER JOIN (
    -- 按Date + Types双维度分组，取每个分组下的最大Report_ID
    SELECT 
        Date, 
        Types,
        MAX(Report_ID) as max_group_report_id
    FROM Table1
    GROUP BY Date, Types
) b 
ON 
    -- 关联时全量匹配分组维度，杜绝跨组错配
    a.Date = b.Date
    AND a.Types = b.Types
    AND a.Report_ID = b.max_group_report_id
ORDER BY Date DESC, Types

如果需要仅查询单个日期的数据，可在外层查询自行补充日期过滤条件，无需修改分组逻辑。

方案2：窗口函数实现（大表场景性能更优） #

SELECT *
FROM (
    SELECT 
        *,
        -- 按Date、Types分组，分组内按Report_ID降序生成排名
        ROW_NUMBER() OVER(PARTITION BY Date, Types ORDER BY Report_ID DESC) AS row_rank
    FROM Table1
) ranked_data
-- 仅保留每个分组内排名第1的记录
WHERE row_rank = 1
ORDER BY Date DESC, Types

场景提示：

• 若同一Date+Types分组下存在多个Report_ID值相同的最高值，使用ROW_NUMBER()会随机返回其中1条记录；如果需要把所有同最高ID的记录全部保留，可将序号函数替换为RANK()

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