SQL Server按多列组合分组取审计表最新记录的实现方法

原写法问题 #

你之前的查询存在两个核心错误，无法得到正确结果：

• 不符合SQL ServerGROUP BY语法规则：非聚合计算的返回字段必须全部加入GROUP BY子句，你仅按拼接的POV字段分组，DimensionA、DimensionB、DimensionC、Amount、UserID既没有做聚合处理也没有加入分组，语句本身无法正常执行。
• 逻辑存在缺陷：就算补全分组字段，MAX(Timestamp)只能拿到每组的最新时间值，无法直接关联到该时间点对应的Amount、UserID字段值，很容易出现字段匹配错位的问题。另外直接拼接三个维度字段生成POV的方式存在边界风险，比如DimensionA='AB',DimensionB='C'和DimensionA='A',DimensionB='BC'拼接后字符串完全一致，会导致错误分组。

正确实现方案 #

你对表只有只读权限的场景下，使用窗口函数ROW_NUMBER()是最稳妥的实现方式，SQL Server 2018完全支持该语法，不需要额外权限：

WITH GroupedAudit AS (
    SELECT
        DimensionA,
        DimensionB,
        DimensionC,
        Amount,
        UserID,
        Timestamp,
        ROW_NUMBER() OVER (
            PARTITION BY DimensionA, DimensionB, DimensionC
            ORDER BY Timestamp DESC
        ) AS GroupRowNo
    FROM AUDIT_TABLE
)
SELECT DimensionA, DimensionB, DimensionC, Amount, UserID, Timestamp
FROM GroupedAudit
WHERE GroupRowNo = 1

逻辑说明 #

• PARTITION BY DimensionA, DimensionB, DimensionC直接按三个维度字段拆分逻辑组，和你的分组规则完全一致，避免字段拼接带来的分组错误。
• 每个分组内按Timestamp倒序排序，组内最新生成的记录序号会被标记为1。
• 外层查询筛选序号为1的记录，即可拿到每个维度组最新的完整行数据，返回字段完全符合需求。

补充说明：如果同一个维度组存在多条Timestamp完全相同的最新记录，上述写法会默认返回其中一条。如果需要返回同时间点的所有最新记录，把ROW_NUMBER()替换为RANK()即可。

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