如何在SQL Server 2016中创建标识重复项的变量及新增列标记重复值

嘿，针对你提到的两个SQL Server 2016的需求，我给你整理了具体的实现方案：

1. 创建用于识别重复项的变量 #

在T-SQL里，我们可以通过声明变量配合聚合/窗口函数来实现重复项的识别，具体看你想要的是统计重复数量还是标记单条记录是否重复：

统计重复记录总数的变量 #

如果想统计列A中所有重复记录的数量（重复的行都会被计入），可以这么写：

-- 声明存储重复记录数的变量
DECLARE @DuplicateRecordCount INT;

-- 计算列A中重复值对应的所有记录数
SELECT @DuplicateRecordCount = COUNT(*)
FROM YourTable
WHERE ColumnA IN (
    SELECT ColumnA
    FROM YourTable
    GROUP BY ColumnA
    HAVING COUNT(*) > 1
);

-- 查看变量结果
SELECT @DuplicateRecordCount AS TotalDuplicateRecords;

标记单条记录是否为重复的变量 #

如果需要检查某条特定记录（比如ID=10的行）是否属于重复项，可以用窗口函数赋值给变量：

DECLARE @IsTargetRecordDuplicate BIT;

-- 检查ID=10的记录对应的列A值是否存在重复
SELECT @IsTargetRecordDuplicate = CASE 
    WHEN COUNT(*) OVER (PARTITION BY ColumnA) > 1 THEN 1 
    ELSE 0 
END
FROM YourTable
WHERE ID = 10;

-- 1表示是重复项，0表示唯一
SELECT @IsTargetRecordDuplicate AS IsDuplicate;

2. 新增列C并标记列A的重复值为'DUP' #

这里有两种常用方法，一种是手动新增列后更新数据，另一种是创建自动维护的计算列：

方法一：新增列后手动更新数据 #

先给表添加列C：

ALTER TABLE YourTable
ADD ColumnC VARCHAR(3) NULL;

然后用CTE结合窗口函数来判断重复并更新：

WITH DuplicateChecker AS (
    SELECT 
        ColumnC,
        -- 统计当前行对应的列A值出现的次数
        COUNT(*) OVER (PARTITION BY ColumnA) AS OccurrenceCount
    FROM YourTable
)
UPDATE DuplicateChecker
SET ColumnC = CASE 
    WHEN OccurrenceCount > 1 THEN 'DUP' 
    ELSE NULL -- 非重复值可以设为NULL或其他标识，比如'UNIQUE'
END;

方法二：创建自动维护的计算列 #

如果希望列C能自动同步列A的重复状态，不需要手动更新，可以创建计算列：

ALTER TABLE YourTable
ADD ColumnC AS CASE 
    WHEN COUNT(*) OVER (PARTITION BY ColumnA) > 1 THEN 'DUP' 
    ELSE NULL 
END;

注意：这种计算列是非持久化的，每次查询时会实时计算。如果需要持久化（把结果存在表中，提升查询性能），可以改用交叉查询的方式：

ALTER TABLE YourTable
ADD ColumnC AS (
    SELECT CASE WHEN COUNT(*) > 1 THEN 'DUP' ELSE NULL END
    FROM YourTable t2
    WHERE t2.ColumnA = YourTable.ColumnA
) PERSISTED;

这样当列A的数据变化时，列C会自动更新。

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