SQL Server多表LEFT JOIN查询返回重复记录的问题求助

原因分析 #

• 多表LEFT JOIN产生笛卡尔积：所有子表（PR、ENQ、CMPRTV等）仅通过JobCardId关联主表JC。如果某子表中对应JobCardId=50075存在多条记录（比如PR有2个采购申请单、ENQ有3个询价单），JOIN操作会让不同子表的记录互相组合，导致主表JC的记录被重复输出，最终结果行数等于各子表记录数的乘积。
• 子表分组后仍有多条记录：每个CTE按JobCardId+单据号分组，会生成对应每个单据号的一条记录。当多个子表都有多条记录时，JOIN会将这些记录全组合，放大结果集产生重复。

解决方法 #

根据业务需求，可选择以下两种处理方式：

方式1：用UNION ALL合并单据记录（适合单独展示每个单据信息）

如果需要列出每个业务单据的详情（每个单据一行），而非将所有单据信息合并到同一行，用UNION ALL替代LEFT JOIN即可避免笛卡尔积。修改后的SQL如下：

WITH JC AS -- Job Card
(   
    SELECT JOBCARDNOSTR AS DocNo,
        'Job Card' AS Document, 
        JobCardId, 
        COUNT(*) AS Count 
    FROM JobCardMaster 
    WHERE JobCardId = 50075
    GROUP BY JobCardId, JOBCARDNOSTR
),
PR AS   -- Purchase Request
(
    SELECT PurchaseRequestNo AS DocNo,
        'Purchase Request' AS Document, 
        JobCardId, 
        COUNT(*) AS Count 
    FROM PurchaseRequestMaster 
    WHERE JobCardId = 50075
    GROUP BY JobCardId, PurchaseRequestNo
),
ENQ AS ---- Enquiry
(
    SELECT enquiryno AS DocNo,
        'Enquiry' AS Document, 
        JobCardId, 
        COUNT(*) AS Count 
    FROM EnquiryMaster 
    WHERE JobCardId = 50075
    GROUP BY JobCardId, enquiryno
),
CMPRTV AS ---- Comparative
(
    SELECT ComparativeNo AS DocNo,
        'Comparative' AS Document, 
        JobCardId,
        COUNT(*) AS Count 
    FROM ComparativeMaster 
    WHERE JobCardId = 50075
    GROUP BY JobCardId, ComparativeNo
),
PO AS ---- Purchase Order 
(
    SELECT poorderno AS DocNo, 'Purchase Order' AS Document, JobCardId, COUNT(*) AS Count 
    FROM PurchaseOrderMaster 
    WHERE JobCardId = 50075
    GROUP BY JobCardId, poorderno
),
GRN as ---- GRN
(
    SELECT GrnGinNo AS DocNo, 'GRN' AS Document, JobCardId, COUNT(*) AS Count 
    FROM Gin 
    WHERE JobCardId = 50075
    GROUP BY JobCardId, GrnGinNo
),
TI as ---- Tax Invoice
(
    SELECT GrnExInvno AS DocNo, 'Tax Invoice' AS Document, JobCardId, COUNT(*) AS Count 
    FROM Gin 
    WHERE JobCardId = 50075
    GROUP BY JobCardId, GrnExInvno
),
DC as -- DC
(
    SELECT dcrefno AS DocNo, 'DC' AS Document, JobCardId, COUNT(*) AS Count 
    FROM DCMaster 
    WHERE JobCardId = 50075
    GROUP BY JobCardId, dcrefno
)
SELECT DocNo, Document, JobCardId, Count
FROM JC
UNION ALL
SELECT DocNo, Document, JobCardId, Count FROM PR
UNION ALL
SELECT DocNo, Document, JobCardId, Count FROM ENQ
UNION ALL
SELECT DocNo, Document, JobCardId, Count FROM CMPRTV
UNION ALL
SELECT DocNo, Document, JobCardId, Count FROM PO
UNION ALL
SELECT DocNo, Document, JobCardId, Count FROM GRN
UNION ALL
SELECT DocNo, Document, JobCardId, Count FROM TI
UNION ALL
SELECT DocNo, Document, JobCardId, Count FROM DC
ORDER BY JobCardId DESC, Document

方式2：聚合子表信息后关联（适合将所有单据信息合并到JobCard一行）

如果需要每个JobCard对应一行，展示各单据类型的总数量和所有单据号（可拼接），先对每个子表聚合，确保每个JobCardId仅返回一行记录，再进行JOIN。修改后的SQL如下：

WITH JC AS -- Job Card
(   
    SELECT JOBCARDNOSTR,
        JobCardId, 
        COUNT(*) AS JCrdCount
    FROM JobCardMaster 
    WHERE JobCardId = 50075
    GROUP BY JobCardId, JOBCARDNOSTR
),
PR_Agg AS
(
    SELECT JobCardId,
           COUNT(*) AS PRCount,
           STRING_AGG(PurchaseRequestNo, ', ') AS PurchaseRequestNos
    FROM PurchaseRequestMaster
    WHERE JobCardId = 50075
    GROUP BY JobCardId
),
ENQ_Agg AS
(
    SELECT JobCardId,
           COUNT(*) AS ENQCount,
           STRING_AGG(enquiryno, ', ') AS EnquiryNos
    FROM EnquiryMaster
    WHERE JobCardId = 50075
    GROUP BY JobCardId
),
CMPRTV_Agg AS
(
    SELECT JobCardId,
           COUNT(*) AS CMPRTVCount,
           STRING_AGG(ComparativeNo, ', ') AS ComparativeNos
    FROM ComparativeMaster
    WHERE JobCardId = 50075
    GROUP BY JobCardId
),
PO_Agg AS
(
    SELECT JobCardId,
           COUNT(*) AS POCount,
           STRING_AGG(poorderno, ', ') AS POOrderNos
    FROM PurchaseOrderMaster
    WHERE JobCardId = 50075
    GROUP BY JobCardId
),
GRN_Agg AS
(
    SELECT JobCardId,
           COUNT(*) AS GRNCount,
           STRING_AGG(GrnGinNo, ', ') AS GrnGinNos
    FROM Gin
    WHERE JobCardId = 50075
    GROUP BY JobCardId
),
TI_Agg AS
(
    SELECT JobCardId,
           COUNT(*) AS TICount,
           STRING_AGG(GrnExInvno, ', ') AS TaxInvoiceNos
    FROM Gin
    WHERE JobCardId = 50075
    GROUP BY JobCardId
),
DC_Agg AS
(
    SELECT JobCardId,
           COUNT(*) AS DCCount,
           STRING_AGG(dcrefno, ', ') AS DCRefNos
    FROM DCMaster
    WHERE JobCardId = 50075
    GROUP BY JobCardId
)
SELECT 
    JC.JCrdCount,
    JC.JOBCARDNOSTR,
    ISNULL(PR_Agg.PRCount, 0) AS PRCount,
    ISNULL(PR_Agg.PurchaseRequestNos, '') AS PurchaseRequestNos,
    ISNULL(ENQ_Agg.ENQCount, 0) AS ENQCount,
    ISNULL(ENQ_Agg.EnquiryNos, '') AS EnquiryNos,
    ISNULL(CMPRTV_Agg.CMPRTVCount, 0) AS CMPRTVCount,
    ISNULL(CMPRTV_Agg.ComparativeNos, '') AS ComparativeNos,
    ISNULL(PO_Agg.POCount, 0) AS POCount,
    ISNULL(PO_Agg.POOrderNos, '') AS POOrderNos,
    ISNULL(GRN_Agg.GRNCount, 0) AS GRNCount,
    ISNULL(GRN_Agg.GrnGinNos, '') AS GrnGinNos,
    ISNULL(TI_Agg.TICount, 0) AS TICount,
    ISNULL(TI_Agg.TaxInvoiceNos, '') AS TaxInvoiceNos,
    ISNULL(DC_Agg.DCCount, 0) AS DCCount,
    ISNULL(DC_Agg.DCRefNos, '') AS DCRefNos
FROM JC
LEFT JOIN PR_Agg ON JC.JobCardId = PR_Agg.JobCardId
LEFT JOIN ENQ_Agg ON JC.JobCardId = ENQ_Agg.JobCardId
LEFT JOIN CMPRTV_Agg ON JC.JobCardId = CMPRTV_Agg.JobCardId
LEFT JOIN PO_Agg ON JC.JobCardId = PO_Agg.JobCardId
LEFT JOIN GRN_Agg ON JC.JobCardId = GRN_Agg.JobCardId
LEFT JOIN TI_Agg ON JC.JobCardId = TI_Agg.JobCardId
LEFT JOIN DC_Agg ON JC.JobCardId = DC_Agg.JobCardId
ORDER BY JC.JobCardId DESC

额外优化建议 #

• 原CTE中的DISTINCT可删除：GROUP BY JobCardId, 单据号已确保每个分组仅返回一条记录，无需额外去重。
• STRING_AGG适用于SQL Server 2017及以上版本，若使用更低版本，可改用STUFF+FOR XML PATH拼接字符串。

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