将两个查询的输出合并为一个查询

Question

我想将以下查询组合成一个输出，我不确定如何使用联合，因为我对查询也有很多条件。在最终输出中，我还需要从查询1的净值列减去查询2的负销售列的结果

SELECT storenumber,
bbbtendertypetext,
SUM(financialamounttendered) - SUM(financialchangeamount) AS NET_VALUE,
COUNT(transactionid) AS Transaction_Count
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
ORDER  BY storenumber 
SELECT storenumber,
bbbtendertypetext,
SUM(financialamounttendered) AS negative_Net_Sales,
COUNT(transactionid) AS Transaction_Count
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
ORDER  BY storenumber 

Answer 1

例如

select s1.storenumber storenumber
, s2.storenumber storenumber2
, s1.bbbtendertypetext bbbtendertypetext
, s2.bbbtendertypetext bbbtendertypetext2
, s1.NET_VALUE  NET_VALUE
, s1.Transaction_Count Transaction_Count
, s2.Transaction_Count Transaction_Count2
, s2.negative_Net_Sales negative_Net_Sales
, s1.NET_VALUE - s2.negative_Net_Sales differnt
from (
SELECT storenumber,
bbbtendertypetext,
SUM(financialamounttendered) - SUM(financialchangeamount) AS NET_VALUE,
COUNT(transactionid) AS Transaction_Count
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
ORDER  BY storenumber 
) s1 left join (
SELECT storenumber,
bbbtendertypetext,
SUM(financialamounttendered) AS negative_Net_Sales,
COUNT(transactionid) AS Transaction_Count
FROM   [AceTLogData].[Tlog].[tender_correct]
AND transactiondatetime > '2022-09-21 00:00:00.000'
AND transactiondatetime < '2022-09-22 00:03:00.000'
GROUP  BY storenumber, bbbtendertypetext
ORDER  BY storenumber
) s2 on s1.storenumber = s2.storenumber

请注意，在第二个查询中没有where部分

Answer 2

假设您想组合查询A和上面查询B的SUM值(唯一值GROUPED BYstorenumber和bbbtendertypetext(，您可以在storenumber上使用两个CTE，然后使用INNER JOIN来获得结果集，并从中进行整体SUM。

如果想要更非聚合的结果集，请从CTE下面的主查询中删除GROUP BY和SUM。

WITH a AS (SELECT storenumber,
bbbtendertypetext,
transactiondatetime,
SUM(financialchangeamount) AS charge_amount,
COUNT(transactionid) AS Transaction_Count
FROM   [AceTLogData].[Tlog].[tender]
GROUP  BY storenumber, bbbtendertypetext, transactiondatetime),
b AS (SELECT storenumber,
bbbtendertypetext,
transactiondatetime,
SUM(financialamounttendered) AS negative_Net_Sales,
COUNT(transactionid) AS Transaction_Count
FROM   [AceTLogData].[Tlog].[tender_correct]
GROUP  BY storenumber, bbbtendertypetext, transactiondatetime)
SELECT a.storenumber,
a.bbbtendertypetext,
SUM(a.charge_amount) AS charge_amount,
SUM(b.negative_Net_Sales) AS negative_Net_Sales ,
SUM(a.charge_amount - b.negative_Net_Sales) AS NET_VALUE,
SUM(a.Transaction_Count) AS Transaction_Count
FROM a INNER JOIN b ON a.storenumber = b.storenumber
WHERE  a.transactiondatetime 
BETWEEN '2022-09-21 00:00:00.000' AND  '2022-09-22 00:03:00.000'
GROUP BY a.storenumber, a.bbbtendertypetext

请参见Fiddle。