如何按(Value1,Value2)分组取Date1最大且Date2最大的记录？

错误原因解释 #

你之前的查询报错是因为：SELECT列表里的Value1、Value2既没有被包含在聚合函数里，也没有放在GROUP BY子句中——窗口函数（比如MAX() OVER()）不会改变SQL中关于非聚合字段的查询规则，数据库无法确定这些字段的取值逻辑，因此抛出8120错误。另外，直接给Value3加OVER子句的写法也不合法，窗口函数必须配合排名函数（如ROW_NUMBER()）或聚合函数使用。

正确实现方案 #

最贴合需求且简洁的方式是使用ROW_NUMBER()窗口函数，通过分区排序标记出每个组内符合条件的目标记录，再筛选出目标记录即可。

方法一：ROW_NUMBER()窗口函数（推荐）

WITH RankedRecords AS (
    SELECT 
        Value1,
        Value2,
        Value3,
        Date1,
        Date2,
        -- 按(Value1,Value2)分组，组内先按Date1降序、再按Date2降序排序，标记序号
        ROW_NUMBER() OVER (
            PARTITION BY Value1, Value2 
            ORDER BY Date1 DESC, Date2 DESC
        ) AS RecordRank
    FROM A
)
-- 筛选每个组内排名第1的记录，就是目标结果
SELECT Value1, Value2, Value3, Date1, Date2
FROM RankedRecords
WHERE RecordRank = 1;

说明

• PARTITION BY Value1, Value2：将数据按(Value1,Value2)组合划分成独立的分组
• ORDER BY Date1 DESC, Date2 DESC：每个分组内，先按Date1从大到小排序；若Date1相同，则按Date2从大到小排序
• ROW_NUMBER()会为每个分组内的记录按排序结果标记序号，符合要求的目标记录序号为1，最后筛选序号为1的记录即可得到期望结果。

方法二：子查询关联（兼容不支持CTE的数据库）

SELECT a.Value1, a.Value2, a.Value3, a.Date1, a.Date2
FROM A a
INNER JOIN (
    -- 先找出每个(Value1,Value2)组的最大Date1，以及该Date1下的最大Date2
    SELECT 
        Value1,
        Value2,
        MAX(Date1) AS MaxDate1,
        MAX(Date2) AS MaxDate2
    FROM A
    GROUP BY Value1, Value2
) b ON a.Value1 = b.Value1 
   AND a.Value2 = b.Value2 
   AND a.Date1 = b.MaxDate1
   AND a.Date2 = b.MaxDate2;

执行结果验证 #

运行上述推荐的ROW_NUMBER()方案后，会得到你期望的结果：

内容的提问来源于stack exchange，提问作者Patrícia Ferreira