三张表基于transactionId关联查询出现重复记录问题咨询

两个从表TransactionsDocs和GenericTransactionTask对同一个transactionId存在多条匹配记录时，左连接会生成笛卡尔积，最终结果行数等于两个从表对应记录数的乘积，从而出现重复：

• 例如transaction表中id=24147的记录共1条，TransactionsDocs表中关联24147的记录有2条，GenericTransactionTask表中关联24147的记录有3条，关联后会生成1*2*3=6条结果，主表id会重复出现6次。

根据你的实际查询需求选择对应方案：

方案1：仅需要去重后的关联记录，不需要聚合 #

直接在查询字段前加DISTINCT即可，适合只需要确认每个事务关联的文档id、任务id对应关系的场景：

select DISTINCT A.Id , B.Id , c.Id 
from  [velvet_elves].[dbo].[Transactions] as A
Left  join [dbo].[TransactionsDocs] as C On A.Id = C.TransactionId
Left  join [dbo].[GenericTransactionTask] as B on A.Id = B.TransactionId
Where A.Id in (24147, 24149)

方案2：需要每个事务对应聚合后的关联文档、任务id列表 #

使用STRING_AGG聚合函数（SQL Server 2017及以上版本支持），将同一个事务关联的所有文档id、任务id合并为逗号分隔的字符串返回：

SELECT 
  A.Id AS TransactionId,
  STRING_AGG(DISTINCT C.Id, ',') AS RelatedTransactionDocIds,
  STRING_AGG(DISTINCT B.Id, ',') AS RelatedGenericTaskIds
FROM [velvet_elves].[dbo].[Transactions] as A
LEFT JOIN [dbo].[TransactionsDocs] as C On A.Id = C.TransactionId
LEFT JOIN [dbo].[GenericTransactionTask] as B on A.Id = B.TransactionId
WHERE A.Id in (24147, 24149)
GROUP BY A.Id

方案3：需要两个从表的记录一一对应展示，避免笛卡尔积 #

先给两个从表同事务id的记录编号，再按编号关联，每个从表的记录只会匹配一次：

WITH NumberedTransactionDocs AS (
    SELECT 
        TransactionId,
        Id AS DocId,
        ROW_NUMBER() OVER(PARTITION BY TransactionId ORDER BY Id) AS RowNum
    FROM [dbo].[TransactionsDocs]
    WHERE TransactionId IN (24147, 24149)
),
NumberedTransactionTasks AS (
    SELECT 
        TransactionId,
        Id AS TaskId,
        ROW_NUMBER() OVER(PARTITION BY TransactionId ORDER BY Id) AS RowNum
    FROM [dbo].[GenericTransactionTask]
    WHERE TransactionId IN (24147, 24149)
)
SELECT 
    A.Id AS TransactionId,
    NTD.DocId,
    NTT.TaskId
FROM [velvet_elves].[dbo].[Transactions] A
LEFT JOIN NumberedTransactionDocs NTD ON A.Id = NTD.TransactionId
LEFT JOIN NumberedTransactionTasks NTT ON A.Id = NTT.TransactionId AND NTD.RowNum = NTT.RowNum
WHERE A.Id IN (24147, 24149)

如果需要保留两个从表中记录数更多的那一侧的所有数据，可以调整两个从表的关联逻辑为FULL JOIN。

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