SQL Server中按多字段分组获取最新记录及对应AlertID的高效方法

嘿，我看了你当前的SQL语句，确实没法直接拿到对应的ALERTID，而且原查询里两次扫描近24小时的数据，效率也有优化空间。下面给你两种高效的解决思路，首选窗口函数的方案，既能拿到ALERTID又能提升查询性能：

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

这种方法只需要扫描一次表数据，通过窗口函数给每个POLY_CODE + ALERT_TYPE分组的记录按时间倒序编号，取编号为1的就是每个分组的最新记录，这样就能直接获取对应的ALERTID和其他字段：

WITH RankedAlerts AS (
    SELECT 
        ALERTID,
        POLY_CODE,
        ALERT_TYPE,
        ALERT_LEVEL,
        ALERT_DATETIME,
        -- 按POLY_CODE和ALERT_TYPE分组，按时间倒序排名
        ROW_NUMBER() OVER (PARTITION BY POLY_CODE, ALERT_TYPE ORDER BY ALERT_DATETIME DESC) AS rn
    FROM TableA
    WHERE ALERT_DATETIME >= GETDATE() - 1 -- 过滤近24小时数据
)
SELECT 
    ALERTID,
    POLY_CODE,
    ALERT_TYPE,
    ALERT_LEVEL AS LAST_ALERT_LEVEL
FROM RankedAlerts
WHERE rn = 1 -- 取每个分组的第一条（最新）记录
ORDER BY POLY_CODE; -- 可选，按需求排序

为什么这个方案更高效？ #

• 仅对TableA做一次过滤扫描，避免了原查询中重复子查询的冗余操作
• 如果你的表上有合适的索引（比如CREATE NONCLUSTERED INDEX IX_TableA_PolyTypeDateTime ON TableA (POLY_CODE, ALERT_TYPE, ALERT_DATETIME DESC) INCLUDE (ALERTID, ALERT_LEVEL);），窗口函数可以直接利用索引完成排序，不需要额外的内存排序，性能会大幅提升

方法二：优化原有的OUTER APPLY写法 #

如果你更倾向于保留类似原查询的结构，也可以修改OUTER APPLY的子查询，让它返回ALERTID和ALERT_LEVEL，然后直接关联取值：

SELECT 
    T2.ALERTID,
    T1.POLY_CODE,
    T1.ALERT_TYPE,
    T2.ALERT_LEVEL AS LAST_ALERT_LEVEL
FROM (
    -- 先获取近24小时内的唯一分组
    SELECT DISTINCT POLY_CODE, ALERT_TYPE 
    FROM TableA 
    WHERE ALERT_DATETIME >= GETDATE() - 1
) T1
OUTER APPLY (
    -- 对每个分组取最新的一条记录，包含ALERTID
    SELECT TOP 1 ALERTID, ALERT_LEVEL 
    FROM TableA 
    WHERE 
        ALERT_DATETIME >= GETDATE() - 1
        AND POLY_CODE = T1.POLY_CODE 
        AND ALERT_TYPE = T1.ALERT_TYPE 
    ORDER BY ALERT_DATETIME DESC
) T2
ORDER BY T1.POLY_CODE;

这个方案的优化点： #

• 用DISTINCT先获取唯一分组，避免原查询中对所有记录做分组操作
• 子查询直接返回ALERTID，不需要额外的关联逻辑

示例结果（包含ALERTID） #

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