如何在已有MAX(exdt)的SQL查询中新增MAX(sqn)筛选获取双最大值记录

可行解决方案 #

方案1：使用窗口函数（推荐，兼容SQL Server、MySQL 8.0+、PostgreSQL等主流数据库） #

通过ROW_NUMBER()窗口函数直接按规则排序取第一条即可，避免多层子查询嵌套：

SELECT item, cpc, dsc, exdt, sqn
FROM (
    SELECT 
        a.item, 
        a.cpc, 
        dt.dsc, 
        a.exdt, 
        a.sqn,
        -- 按item分组，先按exdt倒序，再按sqn倒序，排名第一的就是满足双最大值的记录
        ROW_NUMBER() OVER (PARTITION BY a.item ORDER BY a.exdt DESC, a.sqn DESC) AS rn
    FROM dbo.ct AS a 
    INNER JOIN dbo.dt ON a.cpc = dt.cpc
) AS t
WHERE rn = 1;

如果存在多条记录exdt和sqn都完全相同且都为最大值，需要全部返回的话，把ROW_NUMBER()换成RANK()即可。

方案2：子查询关联方案（兼容不支持窗口函数的低版本数据库） #

先聚合得到每个item的最大exdt、对应最大exdt下的最大sqn，再关联原表取数：

SELECT a.item, a.cpc, dt.dsc, a.exdt, a.sqn
FROM dbo.ct AS a 
INNER JOIN dbo.dt ON a.cpc = dt.cpc
INNER JOIN (
    SELECT 
        item,
        MAX(exdt) AS max_exdt,
        -- 先取最大exdt，再取该exdt下的最大sqn
        (SELECT MAX(sqn) FROM dbo.ct WHERE item = t.item AND exdt = MAX(t.exdt)) AS max_sqn
    FROM dbo.ct AS t
    GROUP BY item
) AS max_val ON a.item = max_val.item AND a.exdt = max_val.max_exdt AND a.sqn = max_val.max_sqn;

原写法报错原因 #

• 原子查询没有闭合，且没有按item维度分组，会直接取全表的最大exdt而非对应item的最大值
• 多层独立的标量子查询嵌套在WHERE条件中，很多数据库对这类语法的支持度有限，且执行效率很低

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