如何合并两个带筛选条件的SQL查询并计算字段差值？

你需要的不是UNION（它用于合并同结构的行记录），而是将两个查询的结果按storenumber和bbbtendertypetext进行关联，再计算差值。用CTE（公用表表达式）拆分逻辑会更清晰：

WITH TenderData AS (
    SELECT 
        storenumber,
        bbbtendertypetext,
        SUM(financialamounttendered) - SUM(financialchangeamount) AS NET_VALUE,
        COUNT(transactionid) AS Transaction_Count_Tender
    FROM [AceTLogData].[Tlog].[tender]
    WHERE transactiondatetime > '2022-09-21 00:00:00.000'
      AND transactiondatetime < '2022-09-22 00:03:00.000'
    GROUP BY storenumber, bbbtendertypetext
),
TenderCorrectData AS (
    SELECT 
        storenumber,
        bbbtendertypetext,
        SUM(financialamounttendered) AS negative_Net_Sales,
        COUNT(transactionid) AS Transaction_Count_Correct
    FROM [AceTLogData].[Tlog].[tender_correct]
    WHERE transactiondatetime > '2022-09-21 00:00:00.000'
      AND transactiondatetime < '2022-09-22 00:03:00.000'
    GROUP BY storenumber, bbbtendertypetext
)
SELECT 
    COALESCE(t.storenumber, tc.storenumber) AS storenumber,
    COALESCE(t.bbbtendertypetext, tc.bbbtendertypetext) AS bbbtendertypetext,
    ISNULL(t.NET_VALUE, 0) - ISNULL(tc.negative_Net_Sales, 0) AS Final_Net_Value,
    ISNULL(t.Transaction_Count_Tender, 0) AS Transaction_Count_Tender,
    ISNULL(tc.Transaction_Count_Correct, 0) AS Transaction_Count_Correct
FROM TenderData t
FULL OUTER JOIN TenderCorrectData tc
    ON t.storenumber = tc.storenumber
    AND t.bbbtendertypetext = tc.bbbtendertypetext
ORDER BY storenumber;

关键细节说明： #

• FULL OUTER JOIN：确保不会遗漏仅在其中一个表存在的storenumber+bbbtendertypetext组合，如果你只需要两个表都有的组合，换成INNER JOIN即可
• ISNULL：处理单侧无数据的NULL情况，用0替代避免减法结果为NULL
• COALESCE：取非NULL的门店号和支付类型，保证输出字段不为空
• 两个子查询保留原时间筛选逻辑，各自过滤后再关联计算

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