查询SQL Server两关联表数据不一致的Select SQL语句编写求助

你可以直接通过聚合查询+关联匹配实现筛选，无需循环，执行效率对1万行的数据集完全足够。

核心思路 #

• 先对Table2按关联字段id1、topic_id分组，统计每组中indicator=1的总条数
• 将统计结果与Table1关联，筛选出当前type标记为multiple，但关联的indicator=1总条数为1的记录，就是你要找的错标数据

筛选错标数据的SQL代码 #

SELECT 
    t1.id1,
    t1.topic_id,
    t1.type AS current_wrong_type,
    t2.indicator_1_count
FROM Table1 t1
INNER JOIN (
    SELECT 
        id1,
        topic_id,
        SUM(indicator) AS indicator_1_count
    FROM Table2
    GROUP BY id1, topic_id
    HAVING SUM(indicator) = 1
) t2 ON t1.id1 = t2.id1 AND t1.topic_id = t2.topic_id
WHERE t1.type = 'multiple'

如果需要同时带出关联的Table2明细用于核对，可以用窗口函数实现：

SELECT * FROM (
    SELECT 
        t1.id1,
        t1.topic_id,
        t1.type AS current_wrong_type,
        t2.id AS table2_id,
        t2.indicator,
        SUM(t2.indicator) OVER(PARTITION BY t2.id1, t2.topic_id) AS indicator_1_count
    FROM Table1 t1
    INNER JOIN Table2 t2 ON t1.id1 = t2.id1 AND t1.topic_id = t2.topic_id
    WHERE t1.type = 'multiple'
) t
WHERE t.indicator_1_count = 1

可选：直接修正错标数据的SQL #

如果你确认筛选结果无误，可以直接用UPDATE语句批量修正，无需额外遍历：

UPDATE t1
SET t1.type = 'single'
FROM Table1 t1
INNER JOIN (
    SELECT id1, topic_id
    FROM Table2
    GROUP BY id1, topic_id
    HAVING SUM(indicator) = 1
) t2 ON t1.id1 = t2.id1 AND t1.topic_id = t2.topic_id
WHERE t1.type = 'multiple'

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