SQL使用UNION时如何保留分支内合法重复并去除跨分支冗余重复

核心规则匹配 #

针对当前业务的去重要求，需同时满足：

• 第二个查询分支（LedgerType_ID = '4'）内部的完全重复记录全部保留，不做去重
• 跨两个分支的完全重复记录全部剔除
• 第一个查询分支（LedgerType_ID = '3'）的内部重复按原有逻辑去重

推荐实现方案（反连接法） #

逻辑最直观且性能可控，实现步骤：

1. 第一个分支结果做内部去重，得到结果集A
2. 第二个分支全量返回不做去重，得到结果集B
3. 从B中剔除所有与A完全匹配的跨分支重复记录，得到结果集B'
4. 合并A与B'得到最终结果

参考SQL代码 #

;WITH Branch3Result AS (
    -- 第一个分支查询，加DISTINCT做内部去重，符合原有业务逻辑
    SELECT DISTINCT
        Com.CompanyCode,
        B.BranchCode,
        P.ProjectCode AS ProjectCode,
        G.GLAccountNo,
        COA.ShortText,
        G.DocCurrency,
        G.DocCurrencyAmount,
        G.Currency AS localCurrecny,
        G.Amount AS LocalAmount,
        S.InvoiceNo AS Reference,
        CAST(S.BillingDocumentNumber AS VARCHAR(100)) AS DocumentNumber,
        '' AS [Document Type],
        CAST(S.BillingDate AS DATE) AS PostingDate,
        CAST(S.PODate AS DATE) AS DocmentDate,
        G.DC,
        '' AS [Clearing Document],
        S.AccountingAssignment AS Assignment,
        '' AS Department,
        '' AS ServiceLineCode,
        '' AS OffSettingAcctNo,
        S.TextDescription AS [Text]
    FROM GLedger G
    LEFT JOIN Company Com ON G.CompanyID = Com.CompanyID
    LEFT JOIN Branch B ON G.BusinessArea = B.BranchCode
    INNER JOIN Sales S ON G.MainID = S.SalesID AND G.LedgerType_ID = '3'
    LEFT JOIN Projects P ON S.AccAssignmentWBSElementID = p.Project_ID
    LEFT JOIN COA ON G.GLAccountNo = COA.GLAccount
    WHERE
        (G.CompanyID = @CompanyID)
        AND (G.GLAccountNo >= @TempGLAccountNoFrom) AND (G.GLAccountNo <= @TempGLAccountNo)
        AND (G.DocumentNumber >= @TempDocumentNumberFrom) AND (G.DocumentNumber <= @TempDocumentNumberTo)
        AND (CAST(S.BillingDate AS DATE) BETWEEN CAST(@TempFromDate AS DATE) AND CAST(@TempToDate AS DATE))
        AND ISNULL(g.Active, 0) = 1
        AND ((G.BusinessArea = @BusinessArea OR @BusinessArea IS NULL))
),
Branch4FullResult AS (
    -- 第二个分支全量查询，不做任何去重，保留所有有效内部重复
    SELECT
        Com.CompanyCode,
        B.BranchCode,
        P.ProjectCode AS ProjectCode,
        G.GLAccountNo,
        COA.ShortText,
        G.DocCurrency,
        G.DocCurrencyAmount,
        G.Currency AS localCurrecny,
        G.Amount AS LocalAmount,
        '' AS Reference,
        CAST(G.DocumentNumber AS VARCHAR(100)) AS DocumentNumber,
        '' AS [Document Type],
        sal.BillingDate AS PostingDate,
        CAST(st.PricingDate AS DATE) AS DocmentDate,
        G.DC,
        '' AS [Clearing Document],
        '' AS Assignment,
        D.DepartmentCode AS Department,
        S.Name AS ServiceLineCode,
        P.ProjectCode AS ProjectCode,
        '' AS OffSettingAcctNo,
        '' AS [Text]
    FROM GLedger G
    LEFT JOIN Company Com ON G.CompanyID = Com.CompanyID
    LEFT JOIN Branch B ON G.BusinessArea = B.BranchCode
    INNER JOIN SalesTransactions ST ON G.SubID = ST.SalesTransactionID AND G.LedgerType_ID = '4'
    LEFT JOIN Department D ON G.DepartmentID = D.DepartmentID
    LEFT JOIN ServiceLineMaster S ON G.ServiceLineID = S.ID
    LEFT JOIN Projects P ON ST.ProjectID = p.Project_ID
    LEFT JOIN COA ON G.GLAccountNo = COA.GLAccount
    INNER JOIN Sales sal ON sal.SalesID = st.SalesID
    WHERE
        (G.CompanyID = @CompanyID)
        AND (G.GLAccountNo >= @TempGLAccountNoFrom) AND (G.GLAccountNo <= @TempGLAccountNo)
        AND (G.DocumentNumber >= @TempDocumentNumberFrom) AND (G.DocumentNumber <= @TempDocumentNumberTo)
        AND (CAST(sal.BillingDate AS DATE) BETWEEN CAST(@TempFromDate AS DATE) AND CAST(@TempToDate AS DATE))
        AND ISNULL(g.Active, 0) = 1
        AND ((G.BusinessArea = @BusinessArea OR @BusinessArea IS NULL))
),
Branch4Filtered AS (
    -- 剔除第二个分支中与第一个分支完全重复的跨分支冗余记录
    SELECT b.*
    FROM Branch4FullResult b
    LEFT JOIN Branch3Result a
    ON
        b.CompanyCode = a.CompanyCode
        AND b.BranchCode = a.BranchCode
        AND b.ProjectCode = a.ProjectCode
        AND b.GLAccountNo = a.GLAccountNo
        AND b.ShortText = a.ShortText
        AND b.DocCurrency = a.DocCurrency
        AND b.DocCurrencyAmount = a.DocCurrencyAmount
        AND b.localCurrecny = a.localCurrecny
        AND b.LocalAmount = a.LocalAmount
        AND ISNULL(b.Reference, '') = ISNULL(a.Reference, '')
        AND b.DocumentNumber = a.DocumentNumber
        AND b.[Document Type] = a.[Document Type]
        AND b.PostingDate = a.PostingDate
        AND b.DocmentDate = a.DocmentDate
        AND b.DC = a.DC
        AND b.[Clearing Document] = a.[Clearing Document]
        AND ISNULL(b.Assignment, '') = ISNULL(a.Assignment, '')
        AND ISNULL(b.Department, '') = ISNULL(a.Department, '')
        AND ISNULL(b.ServiceLineCode, '') = ISNULL(a.ServiceLineCode, '')
        AND b.OffSettingAcctNo = a.OffSettingAcctNo
        AND ISNULL(b.[Text], '') = ISNULL(a.[Text], '')
    WHERE a.CompanyCode IS NULL
)
-- 合并最终结果
SELECT * FROM Branch3Result
UNION ALL
SELECT * FROM Branch4Filtered

注意事项 #

• 原始SQL的第一个分支曾存在重复的ProjectCode列别名，硬编码的空值字段会覆盖真实的项目编码字段，当前代码已修正该问题。
• 跨分支匹配时使用ISNULL处理空值，避免因字段为NULL导致的匹配失效问题。

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