SQL Server 2016如何正确标记DateRan重复记录并更新PartEval字段

问题根源 #

你之前编写的UPDATE语句未给EXISTS子查询添加与外层待更新表的关联条件，只要子查询能返回任意结果，所有符合时间范围的记录都会被判定为满足更新条件，所以才会出现全量标记Dup1的问题。

解决方案 #

方案1：窗口函数法（SQL Server、PostgreSQL、MySQL 8+等主流高版本数据库通用）

优先推荐该方案，逻辑清晰执行效率高。
首先运行查询验证待标记数据是否符合预期：

WITH RankedRecords AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY DateRan, PartEval 
            -- 如需保留某一条指定记录，可修改排序规则，比如按主键ID升序则保留ID最小的记录，行号=1为保留项
            ORDER BY (SELECT 1)
        ) AS rn
    FROM MyTable
    WHERE 
        MachineNr = @MachineNr 
        AND DateRan >= @some_date 
        AND DateRan <= @Some_Date_Plus2
)
SELECT * FROM RankedRecords WHERE rn > 1;

确认数据无误后执行更新：

WITH RankedRecords AS (
    SELECT 
        PartEval,
        ROW_NUMBER() OVER (
            PARTITION BY DateRan, PartEval 
            ORDER BY (SELECT 1)
        ) AS rn
    FROM MyTable
    WHERE 
        MachineNr = @MachineNr 
        AND DateRan >= @some_date 
        AND DateRan <= @Some_Date_Plus2
)
UPDATE RankedRecords 
SET PartEval = 'Dup1' 
WHERE rn > 1;

方案2：关联更新法（兼容低版本不支持窗口函数的数据库）

以MySQL为例，写法如下：

UPDATE MyTable t1
INNER JOIN (
    SELECT 
        DateRan, 
        PartEval,
        -- 保留ID最小的一条记录，其余标记为重复，可根据需求替换为MAX(id)保留最新记录
        MIN(id) AS keep_id 
    FROM MyTable
    WHERE 
        MachineNr = @MachineNr 
        AND DateRan >= @some_date 
        AND DateRan <= @Some_Date_Plus2
    GROUP BY DateRan, PartEval
    HAVING COUNT(*) > 1
) t2 ON t1.DateRan = t2.DateRan 
    AND t1.PartEval = t2.PartEval 
    AND t1.id != t2.keep_id
SET t1.PartEval = 'Dup1';

注意事项 #

• 执行更新操作前务必先备份对应范围的数据，避免误操作导致数据丢失
• 数据量较大时建议分批次执行更新，避免长时间锁表影响正常业务
• 可根据业务需求调整排序/聚合规则，自定义需要保留的重复记录

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