SQL Server多表关联场景下按指定多字段检测重复值的问题求助

问题原因 #

1. 语法错误 #

• JOIN语法书写不规范，FROM [ID_Db] ID , LEFT JOIN、ON ID.[ID]=Des.[ID], 这类写法中多余的逗号会导致SQL解析失败
• 子查询仅返回了category、discount、Price三个字段，不存在ID字段，后续INNER JOIN时使用OS.ID关联属于字段不存在错误，类型转换报错就是因为这里试图用不存在的字段值和INT类型的ID做匹配
• 表名引用不一致：主查询关联的分类表是[CAT]，子查询中错误写为[category]

2. 逻辑错误 #

• 关联逻辑错误：子查询返回的是重复的三个字段组合，应该用这三个字段和主查询做关联，而非错误地关联不存在的ID字段
• LEFT JOIN会引入NULL值，干扰重复值统计逻辑，如果你需要所有字段都有匹配值才纳入统计，使用INNER JOIN更合理

正确实现 #

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

WITH all_data AS (
    SELECT 
        idb.[ID] AS 'ID',
        des.[Price] AS 'price',
        des.[discount] AS 'discount',
        cat.[category] AS 'category',
        na.[name] AS 'name',
        -- 按三个重复维度分组统计每组记录数
        COUNT(*) OVER(PARTITION BY des.[Price], cat.[category], des.[discount]) AS group_cnt
    FROM [ID_Db] idb
    INNER JOIN [Des] des ON idb.[ID] = des.[ID]
    INNER JOIN [CAT] cat ON des.[ID] = cat.[ID]
    INNER JOIN [NA] na ON cat.[ID] = na.[ID]
)
SELECT ID, price, discount, category, name
FROM all_data
WHERE group_cnt > 1 -- 筛选出有重复的分组的所有记录
ORDER BY price, category, discount

方案2：子查询关联写法（修正原有逻辑） #

SELECT 
    idb.[ID] AS 'ID',
    des.[Price] AS 'price',
    des.[discount] AS 'discount',
    cat.[category] AS 'category',
    na.[name] AS 'name'
FROM [ID_Db] idb
INNER JOIN [Des] des ON idb.[ID] = des.[ID]
INNER JOIN [CAT] cat ON des.[ID] = cat.[ID]
INNER JOIN [NA] na ON cat.[ID] = na.[ID]
-- 关联重复的三个字段组合
INNER JOIN (
    SELECT 
        cat_inner.[category],
        des_inner.[discount],
        des_inner.[Price]
    FROM [ID_Db] idb_inner
    INNER JOIN [Des] des_inner ON idb_inner.[ID] = des_inner.[ID]
    INNER JOIN [CAT] cat_inner ON des_inner.[ID] = cat_inner.[ID]
    GROUP BY cat_inner.[category], des_inner.[discount], des_inner.[Price]
    HAVING COUNT(*) > 1
) duplicate_groups
ON des.[Price] = duplicate_groups.[Price]
AND cat.[category] = duplicate_groups.[category]
AND des.[discount] = duplicate_groups.[discount]
ORDER BY des.[Price], cat.[category], des.[discount]

两种方案都可以输出符合要求的重复记录，窗口函数方案性能更优，不需要二次关联表。

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