使用两个CTE加UNION ALL比对数据集出现异常结果，求解决方案

针对你遇到的UNION ALL后差异数量不稳定的问题，尤其是和Premium、Taxes、TimeSpanID列相关的情况，这里有几个更精准、可靠的比对方法，能帮你定位具体差异：

方法1：用EXCEPT和INTERSECT拆分独有的数据 #

这个方法能直接分开查看CTE1有但CTE2没有，以及CTE2有但CTE1没有的记录，比原脚本的GROUP BY方式更直观，还能避免分组时可能的隐式转换或聚合问题：

-- 找出CTE1存在但CTE2不存在的记录
SELECT 'CTE1 Only' AS DifferenceType, *
FROM CTE1
EXCEPT
SELECT 'CTE1 Only' AS DifferenceType, *
FROM CTE2;

-- 找出CTE2存在但CTE1不存在的记录
SELECT 'CTE2 Only' AS DifferenceType, *
FROM CTE2
EXCEPT
SELECT 'CTE2 Only' AS DifferenceType, *
FROM CTE1;

方法2：生成哈希值快速定位列差异 #

针对你怀疑的Premium、Taxes、TimeSpanID列，可以把这些列（加上其他关联键）组合生成哈希值，先通过哈希快速筛选差异行，再深入查看具体列值：

WITH CTE1_Hash AS (
    SELECT 
        BillingAccountID, TransactionID,
        Premium, Taxes, TimeSpanID,
        -- 生成关键列的哈希值
        HASHBYTES('SHA2_256', CONCAT(
            ISNULL(CAST(Premium AS VARCHAR(50)), ''), '|',
            ISNULL(CAST(Taxes AS VARCHAR(50)), ''), '|',
            ISNULL(CAST(TimeSpanID AS VARCHAR(20)), '')
        )) AS KeyColumnsHash
    FROM Fact.SalesBalance1
),
CTE2_Hash AS (
    SELECT 
        BillingAccountID, TransactionID,
        Premium, Taxes, TimeSpanID,
        HASHBYTES('SHA2_256', CONCAT(
            ISNULL(CAST(Premium AS VARCHAR(50)), ''), '|',
            ISNULL(CAST(Taxes AS VARCHAR(50)), ''), '|',
            ISNULL(CAST(TimeSpanID AS VARCHAR(20)), '')
        )) AS KeyColumnsHash
    FROM (
        SELECT *, StartDate=[DateKey], EndDate=ISNULL(MAX([DateKey]) OVER (PARTITION BY [BillingAccountID] ORDER BY DateKey ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), CAST('12/31/9999' AS DATE))
        FROM Fact.SalesTransaction P
        INNER JOIN Dim.SalesTransDate Q ON P.TransactionDateID = Q.CalendarDateID
    ) T
    CROSS APPLY (SELECT TimeSpanID FROM Dim.TimeSpan WHERE StartDate = T.StartDate AND EndDate = T.EndDate)TS
)
-- 比对哈希值不同的行，查看具体列差异
SELECT
    COALESCE(A.BillingAccountID, B.BillingAccountID) AS BillingAccountID,
    COALESCE(A.TransactionID, B.TransactionID) AS TransactionID,
    A.Premium AS CTE1_Premium, B.Premium AS CTE2_Premium,
    A.Taxes AS CTE1_Taxes, B.Taxes AS CTE2_Taxes,
    A.TimeSpanID AS CTE1_TimeSpanID, B.TimeSpanID AS CTE2_TimeSpanID
FROM CTE1_Hash A
FULL JOIN CTE2_Hash B ON A.BillingAccountID = B.BillingAccountID AND A.TransactionID = B.TransactionID
WHERE A.KeyColumnsHash <> B.KeyColumnsHash
OR A.KeyColumnsHash IS NULL OR B.KeyColumnsHash IS NULL;

方法3：全连接逐行比对列值 #

用FULL JOIN直接关联两边的记录，逐列标记差异，能清晰看到每一行中Premium、Taxes、TimeSpanID的具体差异值：

WITH CTE1 AS (
    select [TransactionDateID],[TransactionTypeID],[TransactionID],[BillingAccountID],[BillingInvoiceID],[BillingPaymentID],[Premium],[Taxes],[TimeSpanID]
    from Fact.SalesBalance1
),
CTE2 AS(
    SELECT 
        TransactionDateID=MAX(TransactionDateID) OVER (PARTITION BY [BillingAccountID] ORDER BY [StartDate]),
        TransactionTypeID, TransactionID, BillingAccountID, BillingInvoiceID, BillingPaymentID,
        Premium=SUM(Premium) OVER (PARTITION BY [BillingAccountID] ORDER BY StartDate),
        Taxes=SUM(Taxes) OVER (PARTITION BY [BillingAccountID] ORDER BY StartDate),
        TimeSpanID
    FROM (
        SELECT *, StartDate=[DateKey], EndDate=ISNULL(MAX([DateKey]) OVER (PARTITION BY [BillingAccountID] ORDER BY DateKey ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), CAST('12/31/9999' AS DATE))
        FROM Fact.SalesTransaction P
        INNER JOIN Dim.SalesTransDate Q ON P.TransactionDateID = Q.CalendarDateID
    ) T
    CROSS APPLY (SELECT TimeSpanID FROM Dim.TimeSpan WHERE StartDate = T.StartDate AND EndDate = T.EndDate)TS
)
SELECT
    CASE 
        WHEN A.TransactionID IS NULL THEN 'Missing in CTE1'
        WHEN B.TransactionID IS NULL THEN 'Missing in CTE2'
        ELSE 'Column Mismatch'
    END AS DifferenceType,
    COALESCE(A.BillingAccountID, B.BillingAccountID) AS BillingAccountID,
    COALESCE(A.TransactionID, B.TransactionID) AS TransactionID,
    -- 逐一比对可疑列
    A.Premium AS CTE1_Premium, B.Premium AS CTE2_Premium,
    CASE WHEN A.Premium <> B.Premium THEN 'Different' ELSE 'Same' END AS Premium_Status,
    A.Taxes AS CTE1_Taxes, B.Taxes AS CTE2_Taxes,
    CASE WHEN A.Taxes <> B.Taxes THEN 'Different' ELSE 'Same' END AS Taxes_Status,
    A.TimeSpanID AS CTE1_TimeSpanID, B.TimeSpanID AS CTE2_TimeSpanID,
    CASE WHEN A.TimeSpanID <> B.TimeSpanID THEN 'Different' ELSE 'Same' END AS TimeSpanID_Status
FROM CTE1 A
FULL JOIN CTE2 B 
    ON A.BillingAccountID = B.BillingAccountID 
    AND A.TransactionID = B.TransactionID
WHERE A.TransactionID IS NULL 
OR B.TransactionID IS NULL
OR A.Premium <> B.Premium
OR A.Taxes <> B.Taxes
OR A.TimeSpanID <> B.TimeSpanID
ORDER BY BillingAccountID, TransactionID;

这些方法都能避开原脚本中UNION ALL分组可能带来的不稳定问题，尤其是方法2和3能直接聚焦到你怀疑的那几个列，帮你快速找到差异根源。

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