代码之家 › 专栏 › 技术社区 › mshwf

在相关表上计算聚合函数时,如何避免重复?

tsql sql-server sql

mshwf · 技术社区 · 8 年前

我经常需要对一些相关表进行一些计算,以简化我创建的这三个表的问题:

交易

TransactionId CustomerId  SellerId    NetValue    Taxes
------------- ----------- ----------- ----------- -----------
1             1           2           435         10

交易详细信息

TransactionDetailId TransactionId ItemId      ItemPrice   Qty         IsNew IsOriginal
------------------- ------------- ----------- ----------- ----------- ----- ----------
1                   1             1           40          8           1     1
2                   1             2           50          2           0     1
3                   1             3           15          1           1     0

付款

PaymentId   TransactionId PaymentValue PaymentMethodId
----------- ------------- ------------ ---------------
1           1             50           1
2           1             300          2

现在我需要一个这样的一行表格:

NewItems    NewItemsValue OriginalItems OriginalItemsValue ItemsValue  TotalItems  Paid        Visa        Cash
----------- ------------- ------------- ------------------ ----------- ----------- ----------- ----------- -----------
9           335           10            420                435         11          350         300         50

我第一次尝试这个(在申请之前 SUM 功能):

SELECT
CASE WHEN Detail.IsNew = 1 THEN Detail.Qty END AS NewItems,
CASE WHEN Detail.IsNew = 1 THEN Detail.ItemPrice END AS NewItemsValue,
CASE WHEN Detail.IsOriginal = 1 THEN Detail.Qty END AS OriginalItems,
CASE WHEN Detail.IsOriginal = 1 THEN Detail.ItemPrice END AS OriginalItemsValue,
Pay.PaymentValue AS Paid,
Detail.ItemPrice AS ItemsValue,
Detail.Qty AS TotalItems,
(CASE WHEN Pay.PaymentMethodId=1 THEN Pay.PaymentValue ELSE 0 END) AS Cash,
(CASE WHEN Pay.PaymentMethodId=2 THEN Pay.PaymentValue ELSE 0 END) AS Visa

FROM Transactions AS Trans

JOIN TransactionDetails AS Detail ON Trans.TransactionId = Detail.TransactionId 

JOIN Payments AS Pay 
 ON Trans.TransactionId = Pay.TransactionId

但是有重复的。现在,我唯一的选择是将这三个表与 UNION 但这似乎是一个糟糕的选择,有更好的解决办法吗?

2 回复 | 直到 8 年前

Yogesh Sharma 8 年前

aggregation

SELECT SUM(CASE WHEN Detail.IsNew = 1 THEN Detail.Qty ELSE 0 END) AS NewItems,
       SUM(CASE WHEN Detail.IsNew = 1 THEN Detail.ItemPrice ELSE 0 END) AS NewItemsValue,
       SUM(CASE WHEN Detail.IsOriginal = 1 THEN Detail.Qty END) AS OriginalItems,
       SUM(CASE WHEN Detail.IsOriginal = 1 THEN Detail.ItemPrice ELSE 0 END) AS OriginalItemsValue,
       SUM(Pay.PaymentValue) AS Paid,
       SUM(Detail.ItemPrice) AS ItemsValue,
       SUM(Detail.Qty) AS TotalItems,
       MAX(CASE WHEN Pay.PaymentMethodId=1 THEN Pay.PaymentValue END) AS Cash,
       MAX(CASE WHEN Pay.PaymentMethodId=2 THEN Pay.PaymentValue END) AS Visa
FROM Transactions AS Trans JOIN 
     TransactionDetails AS Detail 
     ON Trans.TransactionId = Detail.TransactionId JOIN 
     Payments AS Pay 
     ON Trans.TransactionId = Pay.TransactionId
GROUP BY Trans.TransactionId;

join

 select TransactionId, 
        sum(case when IsNew = 1 then qty else 0 end) as NewItems,
        sum(case when IsNew = 1 then ItemPrice else 0 end) as NewItemsValue,
        sum(case when IsOriginal = 1 then Qty else 0 end) as OriginalItems,
        sum(case when IsOriginal = 1 then ItemPrice else 0 end) as OriginalItemsValue, count(*) as TotalItems, sum(PaymentValue) as paid
 from TransactionDetails AS Detail 
 group by TransactionId;

mshwf 8 年前

SELECT
(SELECT SUM(Qty) FROM TransactionDetails AS DetailNew WHERE DetailNew.TransactionId=Trans.TransactionId AND IsNew =1) AS NewItems,
(SELECT SUM(ItemPrice*Qty) FROM TransactionDetails AS DetailNew WHERE DetailNew.TransactionId=Trans.TransactionId AND IsNew =1) AS NewItemsValue,
(SELECT SUM(Qty) FROM TransactionDetails AS DetailOriginal WHERE DetailOriginal.TransactionId=Trans.TransactionId AND IsOriginal =1) AS OriginalItems,
(SELECT SUM(ItemPrice*Qty) FROM TransactionDetails AS DetailOriginal WHERE DetailOriginal.TransactionId=Trans.TransactionId AND IsOriginal =1) AS OriginalItemsValue,
(SELECT SUM(ItemPrice*Qty) FROM TransactionDetails AS Detail WHERE Detail.TransactionId=Trans.TransactionId) AS ItemsValue,
(SELECT SUM(Qty) FROM TransactionDetails) AS TotalItems,
(SELECT SUM(PaymentValue) FROM Payments) AS Paid,
(SELECT SUM(PaymentValue) FROM Payments WHERE PaymentMethodId=2) AS Visa,
(SELECT SUM(PaymentValue) FROM Payments WHERE PaymentMethodId=1) AS Cash
FROM Transactions AS Trans