SQL Server 2016中筛选特定条件重复记录的技术问询

首先，我完全理解你的需求：要从数据集中筛选出满足以下两个条件之一的重复记录——这些记录必须在LName、FName、DateOfBirth、StreetAddress四个字段上完全匹配（属于同一重复组），同时：

1. 记录的Source字段为NULL；
2. 记录的Source字段为'Company XYZ'。

你的原代码已经能识别出四个字段重复的组，但缺少对Source字段的筛选逻辑，而且左连接min(ID)的部分没有实际作用，导致输出不符合预期。下面是两种优化后的解决方案：

方法一：使用关联子查询（兼容SQL Server 2016） #

这种方式先找出所有重复组，再关联回原表并筛选Source条件，逻辑清晰易懂：

IF OBJECT_ID('tempdb..#Dataset') IS NOT NULL DROP TABLE #Dataset
GO
create table #Dataset (
 ID int not null,
 LName varchar(50) null,
 Fname varchar(50) null,
 DateOfBirth varchar(50) null,
 StreetAddress varchar(50) null,
 Source varchar(50) null,
)
insert into #Dataset (ID, LName, Fname, DateOfBirth, StreetAddress, Source)
values
('1', 'John', 'Ganske', '37171', ' 1223 Sunrise St', 'Company XYZ'),
('2', 'John', 'Ganske', '37171', ' 1233 Sunrise St', 'Company XYZ'),
('4', 'Brent', 'Paine', '20723', ' 5443 Fox Dr', Null),
('3', 'Brent', 'Paine', '20723', ' 5443 Fox Dr', 'Company XYZ'),
('5', 'Adam', 'Smith', '22805', ' 1254 Lake Ridge Ct', Null),
('6', 'Adam', 'Smith', '22805', ' 1254 Lake Ridge Ct', Null),
('7', 'Adam', 'Smith', '22805', ' 1254 Lake Ridge Ct', 'Company XYZ'),
('8', 'Timothy', 'Johnson', '36165', ' 1278 Lee H-W', Null),
('9', 'Timothy', 'Johnson', '36165', ' 1278 Lee H-W', Null),
('10', 'Judy', 'Wilson', '32579', ' 5678 Dotties Dr', 'Company XYZ'),
('12', 'Peter', 'Pan', '37507', NULL, Null),
('11', 'Peter', 'Pan', '37507', NULL, 'Company XYZ');

-- 筛选符合条件的重复记录
SELECT d.ID, d.LName, d.Fname, d.DateOfBirth, d.StreetAddress, d.Source
FROM #Dataset d
INNER JOIN (
    -- 找出四个字段重复的组（至少两条记录）
    SELECT LName, Fname, DateOfBirth, StreetAddress
    FROM #Dataset
    GROUP BY LName, Fname, DateOfBirth, StreetAddress
    HAVING COUNT(*) > 1
) b 
    ON d.LName = b.LName 
    AND d.Fname = b.Fname 
    AND d.DateOfBirth = b.DateOfBirth 
    AND d.StreetAddress = b.StreetAddress
-- 筛选Source条件
WHERE d.Source IS NULL OR d.Source = 'Company XYZ';

方法二：使用窗口函数（更简洁高效） #

窗口函数可以直接在原表上标记每个组的记录数量，一步完成筛选，性能更优：

SELECT ID, LName, Fname, DateOfBirth, StreetAddress, Source
FROM (
    SELECT 
        *,
        -- 计算每个组的记录总数
        COUNT(*) OVER (PARTITION BY LName, Fname, DateOfBirth, StreetAddress) AS GroupCount
    FROM #Dataset
) t
-- 只保留重复组（GroupCount>1）且Source符合条件的记录
WHERE GroupCount > 1 
  AND (Source IS NULL OR Source = 'Company XYZ');

预期输出结果 #

执行上述代码后，你会得到以下符合需求的记录：

这样就精准筛选出了你需要的两类重复记录，排除了像John Ganske这类四个字段不匹配的记录，以及Judy Wilson这类没有重复的记录。

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