SQL Server 2008：保留连续Batch组内最大Weight记录，删除其余

需求说明 #

现有SQL Server 2008数据表tblTest，包含Id、Weight、Batch、t_stamp字段，存在误写入的冗余记录。需按以下规则清理：

• 连续相同的Batch记录视为独立组，非连续的同Batch记录不合并（例如Batch=2的记录分为3个独立组）
• 每组仅保留Weight值最大的记录，其余冗余记录删除

表结构 #

CREATE TABLE tblTest
(
    Id INT PRIMARY KEY NOT NULL,
    Weight INT NULL,
    Batch INT NULL,
    t_stamp DATETIME NULL
);

测试数据 #

注：原插入语句表名存在笔误，已修正为tblTest，字段对应关系调整为与表结构匹配：

INSERT INTO tblTest (Id, Weight, Batch, t_stamp)
VALUES (31, 3994, 2, '2023-12-05 06:19:58.990'),
       (32, 4052, 2, '2023-12-05 06:37:24.883'),
       (33, 4000, 5, '2023-12-05 08:44:09.780'),
       (34, 4058, 5, '2023-12-05 08:58:59.683'),
       (35, 4032, 8, '2023-12-05 11:18:11.727'),
       (36, 3983, 11, '2023-12-05 13:20:35.167'),
       (37, 4013, 11, '2023-12-05 13:33:54.877'),
       (38, 3993, 14, '2023-12-05 15:29:50.060'),
       (39, 3470, 14, '2023-12-05 15:48:51.053'),
       (40, 3996, 14, '2023-12-05 17:20:09.893'),
       (41, 3348, 14, '2023-12-05 17:39:22.477'),
       (42, 3993, 2, '2023-12-05 19:48:42.117'),
       (43, 4054, 2, '2023-12-05 20:09:12.217'),
       (44, 3991, 23, '2023-12-05 22:55:45.020'),
       (45, 4065, 23, '2023-12-05 23:16:12.077'),
       (46, 3993, 26, '2023-12-06 02:56:29.713'),
       (47, 4033, 26, '2023-12-06 03:12:24.483'),
       (48, 3982, 26, '2023-12-06 06:06:45.720'),
       (49, 4075, 26, '2023-12-06 06:35:05.197'),
       (50, 3994, 2, '2023-12-06 08:02:58.887'),
       (51, 3029, 2, '2023-12-06 08:28:21.260'),
       (52, 3982, 2, '2023-12-06 09:49:23.497'),
       (53, 4038, 7, '2023-12-06 10:12:41.707')

解决方案 #

由于SQL Server 2008不支持LAG()/LEAD()函数，我们通过行号差值法标识连续的Batch组，再筛选每组内Weight最大的记录。

步骤1：查询验证保留的记录 #

先执行以下查询确认要保留的记录，避免误删：

WITH GroupedRecords AS (
    SELECT 
        Id,
        Weight,
        Batch,
        t_stamp,
        -- 生成连续组标识：全局行号 - 按Batch分组的行号
        ROW_NUMBER() OVER (ORDER BY t_stamp) 
        - ROW_NUMBER() OVER (PARTITION BY Batch ORDER BY t_stamp) AS GroupId
    FROM tblTest
),
MaxWeightPerGroup AS (
    SELECT 
        GroupId,
        Batch,
        MAX(Weight) AS MaxWeight
    FROM GroupedRecords
    GROUP BY GroupId, Batch
)
SELECT gr.Id, gr.Weight, gr.Batch, gr.t_stamp
FROM GroupedRecords gr
JOIN MaxWeightPerGroup mwpg ON gr.GroupId = mwpg.GroupId AND gr.Batch = mwpg.Batch AND gr.Weight = mwpg.MaxWeight
ORDER BY gr.t_stamp;

步骤2：删除冗余记录 #

确认结果正确后，执行删除操作：

WITH GroupedRecords AS (
    SELECT 
        Id,
        Weight,
        Batch,
        ROW_NUMBER() OVER (ORDER BY t_stamp) 
        - ROW_NUMBER() OVER (PARTITION BY Batch ORDER BY t_stamp) AS GroupId
    FROM tblTest
),
MaxWeightPerGroup AS (
    SELECT 
        GroupId,
        Batch,
        MAX(Weight) AS MaxWeight
    FROM GroupedRecords
    GROUP BY GroupId, Batch
),
RecordsToKeep AS (
    SELECT gr.Id
    FROM GroupedRecords gr
    JOIN MaxWeightPerGroup mwpg ON gr.GroupId = mwpg.GroupId AND gr.Batch = mwpg.Batch AND gr.Weight = mwpg.MaxWeight
)
DELETE FROM tblTest
WHERE Id NOT IN (SELECT Id FROM RecordsToKeep);

补充说明 #

如果同一组内存在多条Weight相同的最大值记录，上述代码会保留所有这些记录。若需仅保留其中一条（例如最新t_stamp或最小Id），可修改RecordsToKeep部分：

RecordsToKeep AS (
    SELECT 
        gr.Id
    FROM (
        SELECT 
            gr.Id,
            ROW_NUMBER() OVER (PARTITION BY gr.GroupId, gr.Batch ORDER BY gr.t_stamp DESC) AS rn
        FROM GroupedRecords gr
        JOIN MaxWeightPerGroup mwpg ON gr.GroupId = mwpg.GroupId AND gr.Batch = mwpg.Batch AND gr.Weight = mwpg.MaxWeight
    ) gr
    WHERE gr.rn = 1
)

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