SQL对比两表列值时出现重复不匹配行的问题求助

你遇到的重复行问题，本质是两张表中存在同一个AgentRepresentation对应多条记录，全外连接时会产生笛卡尔积，导致同一AgentRepresentation被多次配对，输出大量重复结果。

核心解决方案：先清理表数据，确保每个AgentRepresentation仅保留一条记录 #

在进行全外连接前，先对两张表分别处理，去除同一AgentRepresentation的重复记录（或按业务规则保留一条有效记录），再执行对比逻辑。

方案1：用窗口函数去重（推荐，可灵活指定保留规则）

如果需要按特定规则（比如最新记录）保留一条，用ROW_NUMBER()窗口函数给每个AgentRepresentation分组编号，只取编号为1的记录：

WITH fcubs_clean AS (
    SELECT 
        AgentRepresentation,
        IsActive,
        Scheme,
        Routing,
        -- 按业务需要调整ORDER BY字段，比如创建时间取最新
        ROW_NUMBER() OVER (PARTITION BY AgentRepresentation ORDER BY (SELECT NULL)) AS rn
    FROM [cpe/NetworkReachability/fcubs-NetworkReachability]
),
cepos_clean AS (
    SELECT 
        AgentRepresentation,
        IsActive,
        Scheme,
        Routing,
        ROW_NUMBER() OVER (PARTITION BY AgentRepresentation ORDER BY (SELECT NULL)) AS rn
    FROM [cpe/NetworkReachability/cepos-NetworkReachability]
)
SELECT 
    CASE 
        WHEN fcubs.AgentRepresentation IS NULL THEN 'missing in fcubs'
        WHEN cepos.AgentRepresentation IS NULL THEN 'missing in cepos'
        WHEN fcubs.IsActive <> cepos.IsActive THEN 'IsActive mismatch'
        WHEN fcubs.Scheme <> cepos.Scheme THEN 'Scheme mismatch'
        WHEN fcubs.Routing <> cepos.Routing THEN 'Routing mismatch'
        ELSE 'matched'
    END AS ReconciliationStatus,
    COALESCE(fcubs.AgentRepresentation, cepos.AgentRepresentation) AS AgentRepresentation,
    fcubs.IsActive AS fcubsIsActive,
    cepos.IsActive AS ceposIsActive,
    fcubs.Scheme AS fcubsScheme,
    cepos.Scheme AS ceposScheme,
    fcubs.Routing AS fcubsRouting,
    cepos.Routing AS ceposRouting
FROM fcubs_clean fcubs
FULL OUTER JOIN cepos_clean cepos 
    ON fcubs.AgentRepresentation = cepos.AgentRepresentation
WHERE (fcubs.rn = 1 OR fcubs.rn IS NULL)
  AND (cepos.rn = 1 OR cepos.rn IS NULL)
  AND (fcubs.AgentRepresentation IS NULL 
       OR cepos.AgentRepresentation IS NULL 
       OR NOT (fcubs.IsActive = cepos.IsActive 
               AND fcubs.Scheme = cepos.Scheme 
               AND fcubs.Routing = cepos.Routing))
ORDER BY AgentRepresentation;

方案2：用GROUP BY聚合（适合同一AgentRepresentation字段值一致的场景）

如果同一AgentRepresentation对应的IsActive、Scheme、Routing字段值理论上应该完全一致，可以用GROUP BY聚合去重：

WITH fcubs_clean AS (
    SELECT 
        AgentRepresentation,
        MAX(IsActive) AS IsActive,
        MAX(Scheme) AS Scheme,
        MAX(Routing) AS Routing
    FROM [cpe/NetworkReachability/fcubs-NetworkReachability]
    GROUP BY AgentRepresentation
),
cepos_clean AS (
    SELECT 
        AgentRepresentation,
        MAX(IsActive) AS IsActive,
        MAX(Scheme) AS Scheme,
        MAX(Routing) AS Routing
    FROM [cpe/NetworkReachability/cepos-NetworkReachability]
    GROUP BY AgentRepresentation
)
SELECT 
    CASE 
        WHEN fcubs.AgentRepresentation IS NULL THEN 'missing in fcubs'
        WHEN cepos.AgentRepresentation IS NULL THEN 'missing in cepos'
        WHEN fcubs.IsActive <> cepos.IsActive THEN 'IsActive mismatch'
        WHEN fcubs.Scheme <> cepos.Scheme THEN 'Scheme mismatch'
        WHEN fcubs.Routing <> cepos.Routing THEN 'Routing mismatch'
        ELSE 'matched'
    END AS ReconciliationStatus,
    COALESCE(fcubs.AgentRepresentation, cepos.AgentRepresentation) AS AgentRepresentation,
    fcubs.IsActive AS fcubsIsActive,
    cepos.IsActive AS ceposIsActive,
    fcubs.Scheme AS fcubsScheme,
    cepos.Scheme AS ceposScheme,
    fcubs.Routing AS fcubsRouting,
    cepos.Routing AS ceposRouting
FROM fcubs_clean fcubs
FULL OUTER JOIN cepos_clean cepos 
    ON fcubs.AgentRepresentation = cepos.AgentRepresentation
WHERE fcubs.AgentRepresentation IS NULL 
    OR cepos.AgentRepresentation IS NULL 
    OR NOT (fcubs.IsActive = cepos.IsActive 
            AND fcubs.Scheme = cepos.Scheme 
            AND fcubs.Routing = cepos.Routing)
ORDER BY AgentRepresentation;

额外优化点 #

• 将原查询的ELSE 'Unknown condition'改为'matched'，逻辑更准确：前面的条件覆盖了缺失和所有字段不匹配的情况，剩余就是匹配状态。
• 如果业务上允许，可先检查两张表中是否存在AgentRepresentation重复的记录，这才是重复行的根源。

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