如何用单SELECT语句替代CTE实现分组取max(date_col2)对应行

针对你需要按id、date_col1分组，每组保留date_col2最大（最新）的行及对应value的需求，这里提供两种无需CTE的单SELECT语句实现方式，性能通常优于CTE写法：

方法1：窗口函数ROW_NUMBER()（推荐大数据量场景） #

利用窗口函数对分组内的行按date_col2降序排序，标记最新行为序号1，再过滤出序号为1的行：

SELECT id, date_col1, date_col2, value
FROM (
    SELECT 
        id, date_col1, date_col2, value,
        ROW_NUMBER() OVER (PARTITION BY id, date_col1 ORDER BY date_col2 DESC) AS row_rank
    FROM sample_table
) ranked_rows
WHERE row_rank = 1;

• 注意：如果同一分组内存在多个date_col2等于最大值的行，ROW_NUMBER()会随机选取其中一行；若要保留所有这类行，将ROW_NUMBER()替换为RANK()即可。
• 性能优势：仅需扫描一次数据表，配合(id, date_col1, date_col2 DESC)的联合索引，查询效率会大幅提升。

方法2：关联子查询 #

通过子查询获取每组的最大date_col2，再关联原表匹配对应行：

SELECT s.id, s.date_col1, s.date_col2, s.value
FROM sample_table s
WHERE s.date_col2 = (
    SELECT MAX(date_col2)
    FROM sample_table
    WHERE id = s.id AND date_col1 = s.date_col1
);

• 注意：如果同一分组内有多个行的date_col2等于最大值，该查询会返回所有这些行；若只需唯一行，可结合LIMIT 1或额外的排序字段（如主键）来控制。
• 性能提示：若表上存在(id, date_col1, date_col2)的联合索引，子查询会直接命中索引，避免全表扫描。

验证示例 #

假设你的样本数据表如下：

使用上述两种方法均可得到期望结果：

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