SQL Server技术问询：查询包含关系值及t1表重复数据修复

嘿，我来帮你搞定这个SQL Server的数据匹配难题！先理清楚你的场景：
表t1里存在两类属于同一人员的重复数据：

• 一类记录的ID对应的Name/FirstName是空的，但FullName字段有完整姓名
• 另一类记录是同一人员用其他ID存储的，此时FullName为空，但Name和FirstName有对应值
  而且坑人的是，Name或FirstName里还有空格填充的情况，直接拼接这两个字段去匹配FullName肯定会出错。

第一步：先搞定多余空格的问题 #

首先得把字段里的冗余空格清理干净——不管是首尾的空格，还是中间的多个连续空格。我写了个自定义函数来干这个活，比反复写LTRIM/RTRIM方便多了：

-- 自定义函数：清理字符串中的所有多余空格（首尾+中间多空格转单空格）
CREATE FUNCTION dbo.CleanWhitespace(@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    -- 先去掉首尾空格
    SET @input = LTRIM(RTRIM(@input))
    -- 循环替换中间的多个空格为单个空格，直到没有连续空格为止
    WHILE CHARINDEX('  ', @input) > 0
        SET @input = REPLACE(@input, '  ', ' ')
    RETURN @input
END
GO

第二步：关联两类重复数据，显示同一人员的全部记录 #

接下来就用这个函数来做精准匹配。假设你的FullName是FirstName + 空格 + Name的格式（如果是反过来的话，调整下拼接顺序就行），我们可以写个关联查询，把同一人员的两条记录都拉出来：

-- 查询同一人员的全部数据：关联FullName非空的记录和Name/FirstName非空的记录
SELECT 
    -- 给两类记录的字段加个前缀，方便区分
    full_rec.ID AS ID_WithFullName,
    full_rec.FullName,
    name_rec.ID AS ID_WithNames,
    name_rec.FirstName,
    name_rec.Name,
    -- 其他需要显示的字段可以继续加在这里
    full_rec.OtherField AS Field_FromFullNameRec,
    name_rec.OtherField AS Field_FromNameRec
FROM 
    t1 full_rec
JOIN 
    t1 name_rec ON 
        -- 两边都清理空格后再匹配
        dbo.CleanWhitespace(full_rec.FullName) = 
        dbo.CleanWhitespace(CONCAT(name_rec.FirstName, ' ', name_rec.Name))
WHERE 
    -- 筛选出FullName非空但Name/FirstName为空的记录
    full_rec.FullName IS NOT NULL 
    AND full_rec.Name IS NULL 
    AND full_rec.FirstName IS NULL
    -- 匹配的是Name/FirstName非空但FullName为空的记录
    AND name_rec.FullName IS NULL 
    AND name_rec.Name IS NOT NULL 
    AND name_rec.FirstName IS NOT NULL

如果你的FullName格式是Name + 空格 + FirstName，只需要把CONCAT里的两个字段顺序调换就行：CONCAT(name_rec.Name, ' ', name_rec.FirstName)

可选：合并两类数据为完整记录 #

如果你不想分开看两条记录，而是想把它们合并成一条包含完整信息的记录，可以用COALESCE函数来取非空的值：

-- 合并同一人员的两类数据，生成完整记录
SELECT 
    -- 这里可以根据需求选择保留哪个ID，或者都保留
    full_rec.ID AS OriginalID,
    name_rec.ID AS DuplicateID,
    -- 取清理后的完整姓名（优先用FullName字段的值）
    COALESCE(dbo.CleanWhitespace(full_rec.FullName), dbo.CleanWhitespace(CONCAT(name_rec.FirstName, ' ', name_rec.Name))) AS FullName_Clean,
    -- 取非空的FirstName和Name
    COALESCE(name_rec.FirstName, '') AS FirstName,
    COALESCE(name_rec.Name, '') AS Name,
    -- 其他字段同理，用COALESCE合并两个记录的非空值
    COALESCE(full_rec.Email, name_rec.Email) AS Email,
    COALESCE(full_rec.Phone, name_rec.Phone) AS Phone
FROM 
    t1 full_rec
JOIN 
    t1 name_rec ON 
        dbo.CleanWhitespace(full_rec.FullName) = 
        dbo.CleanWhitespace(CONCAT(name_rec.FirstName, ' ', name_rec.Name))
WHERE 
    full_rec.FullName IS NOT NULL 
    AND full_rec.Name IS NULL 
    AND full_rec.FirstName IS NULL
    AND name_rec.FullName IS NULL 
    AND name_rec.Name IS NOT NULL 
    AND name_rec.FirstName IS NOT NULL

性能优化小技巧（如果数据量大的话） #

如果你的表数据量很大，自定义函数可能会拖慢查询速度。这时候可以先把清理后的字段存入临时表，再做关联，效率会高很多：

-- 先把清理后的数据存入临时表
SELECT 
    ID,
    FullName,
    Name,
    FirstName,
    dbo.CleanWhitespace(FullName) AS FullName_Clean,
    dbo.CleanWhitespace(CONCAT(FirstName, ' ', Name)) AS CombinedNames_Clean
INTO #CleanedT1
FROM t1

-- 用临时表做关联查询
SELECT 
    full_rec.ID AS ID_WithFullName,
    name_rec.ID AS ID_WithNames,
    full_rec.FullName,
    name_rec.FirstName,
    name_rec.Name
FROM #CleanedT1 full_rec
JOIN #CleanedT1 name_rec ON full_rec.FullName_Clean = name_rec.CombinedNames_Clean
WHERE 
    full_rec.FullName IS NOT NULL 
    AND full_rec.Name IS NULL 
    AND full_rec.FirstName IS NULL
    AND name_rec.FullName IS NULL 
    AND name_rec.Name IS NOT NULL 
    AND name_rec.FirstName IS NOT NULL

-- 用完记得删除临时表
DROP TABLE #CleanedT1

这样应该就能完美解决你的问题了——既处理了空格的干扰，又能把同一人员的所有数据都找出来，甚至可以合并成完整记录~

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