T-SQL多对多无主键关联查询如何避免T5.FID重复匹配结果

问题根因 #

原查询中OUTER APPLY子句对每一条主关联结果行独立随机选取1条T5记录，没有全局去重约束，因此多条主行可能匹配到同一条T5记录，导致T5.FID重复；同时原查询中T3.PID = T3.PID是恒真冗余条件，无实际作用。

修改后查询语句 #

WITH MainRows AS (
    -- 获取所有基础关联结果，为同BID下的每行分配唯一序号
    SELECT 
        T1.ID,
        T2.BID,
        T2.FID AS T2_FID,
        T3.FID AS T3_FID,
        T3.PID,
        T4.Name,
        T2.IsDone,
        ROW_NUMBER() OVER(PARTITION BY T2.BID ORDER BY T2.FID) AS RN
    FROM T1
    INNER JOIN T2 ON T2.BID = T1.ID
    INNER JOIN T3 ON T3.FID = T2.FID
    INNER JOIN T4 ON T4.PID = T3.PID
),
T5_Ranked AS (
    -- 为符合条件的T5行按BID分组分配唯一序号，可自行调整排序规则
    SELECT 
        T5.FID AS T5_FID,
        T5.BID,
        ROW_NUMBER() OVER(PARTITION BY T5.BID ORDER BY NEWID()) AS RN
    FROM T5
    INNER JOIN T3 ON T3.FID = T5.FID
)
SELECT 
    m.ID,
    m.BID,
    m.T2_FID,
    m.T3_FID,
    m.PID,
    m.PID AS T4_PID,
    m.Name,
    CASE WHEN m.IsDone = 1 THEN t.T5_FID ELSE NULL END AS T5_FID
FROM MainRows m
LEFT JOIN T5_Ranked t ON m.BID = t.BID AND m.RN = t.RN

方案说明 #

• 用ROW_NUMBER()窗口函数分别为主关联行、T5行按BID分组分配独立序号，通过序号一一对应匹配，保证每个T5.FID仅会被使用一次，不会出现重复
• 如果需要自定义T5的匹配优先级，可以修改T5_Ranked中ORDER BY的排序规则，不需要随机匹配就删掉NEWID()替换为实际业务字段即可
• 当同BID下主关联行数量大于T5可用行数时，超出部分的T5相关字段返回NULL，符合左连接语义；同时保留了原逻辑中IsDone=0的行T5字段返回NULL的规则

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