SQL联接没有给出正确的总数。

Question

我正试图从多个事务表中获得一份数据报告。每个表都有一个外键，用于查找它所使用的一天，以及它在什么位置被称为Site_Lookup_Id。它给我的数字比他们应该的要大得多。

@Site_Lookup_Ids dbo.Id_List READONLY

SELECT SL.Site_Lookup_Id, D.[Start],SUM(I.Amount) AS Income, 
       SUM(P.Amount) AS Payouts,SUM(DP.Amount) AS Deposit 
FROM CashUp_Site_Lookup SL
INNER JOIN @Site_Lookup_Ids IDs ON Ids.Id = SL.Site_Lookup_Id
INNER JOIN CashUp_Day D ON SL.Day_Id = D.Day_Id
LEFT JOIN CashUp_Deposit DP ON DP.Redeemed_Site_Lookup_Id = SL.Site_Lookup_Id 
                            AND DP.No_Show != 1
LEFT JOIN CashUp_Income I ON I.Site_Lookup_Id = SL.Site_Lookup_Id
LEFT JOIN CashUp_Payout P ON P.Site_Lookup_Id = SL.Site_Lookup_Id
GROUP BY SL.Site_Lookup_Id, D.[Start]

并不是所有的和都有一个值，因为有些日子，给定表的事务不会在某一天进行--在这种情况下，值应该是零。

问题是，运行这给我提供了疯狂的高价值-‘s 7500的收入相对于某一天，而如果我做一个简单的检查，它是国标40的那一天是这样。

SELECT SUM(Amount) FROM Cashup_Income WHERE Site_Lookup_Id IN (values...)

Answer 1

也许有点像..。

这实际上取决于关系，以及您想要将值求和的时间。

SELECT SL.Site_Lookup_Id
     , D.[Start]
     , SUM(I.Amount) over (partition by Key of I table) AS Income
     , SUM(P.Amount) over (partition by Key of P table) AS Payouts
     , SUM(DP.Amount) over (partition by Key of DP Table) AS Deposit 
FROM CashUp_Site_Lookup SL
INNER JOIN @Site_Lookup_Ids IDs ON Ids.Id = SL.Site_Lookup_Id
INNER JOIN CashUp_Day D ON SL.Day_Id = D.Day_Id
LEFT JOIN CashUp_Deposit DP ON DP.Redeemed_Site_Lookup_Id = SL.Site_Lookup_Id 
                            AND DP.No_Show != 1
LEFT JOIN CashUp_Income I ON I.Site_Lookup_Id = SL.Site_Lookup_Id
LEFT JOIN CashUp_Payout P ON P.Site_Lookup_Id = SL.Site_Lookup_Id
GROUP BY SL.Site_Lookup_Id, D.[Start]

问题的根源在于表是1:M，导致值重复。然后将这些重复的值添加到您的和中。加入导致了这个问题。因此，我认为您可以使用一个分区来消除重复项，或者：

使用派生表或CTE，并在加入之前对值进行求和。

使用CTE(通用表表达式)

WITH DP AS (SELECT sum(Amount) As Deposit 
                 , Redeemed_Site_Lookup_ID
            FROM CashUp_Deposit
            WHERE No_Show !=1
            GROUP BY Redeemed_Site_Lookup_ID),
     I  AS (SELECT sum(Amount) as Income
                 , Site_Lookup_Id 
            FROM CashUp_Income 
            GROUP BY Site_Lookup_Id),
     P AS (SELECT sum(Amount) as Payouts
                 , Site_Lookup_Id 
            FROM CashUp_Payout 
            GROUP BY Site_Lookup_Id)

SELECT SL.Site_Lookup_Id
     , D.[Start]
     , Income
     , Payouts
     , Deposit 
FROM CashUp_Site_Lookup SL
INNER JOIN @Site_Lookup_Ids IDs 
        ON Ids.Id = SL.Site_Lookup_Id
INNER JOIN CashUp_Day D 
        ON SL.Day_Id = D.Day_Id
LEFT JOIN DP 
       ON DP.Redeemed_Site_Lookup_Id = SL.Site_Lookup_Id 
LEFT JOIN I 
       ON I.Site_Lookup_Id = SL.Site_Lookup_Id
LEFT JOIN P 
       ON P.Site_Lookup_Id = SL.Site_Lookup_Id

Answer 2

想必，您正在生成一个笛卡尔乘积与您的联接。由于没有筛选，所以在joins之前进行聚合：

LEFT JOIN
(SELECT DP.Redeemed_Site_Lookup_Id, SUM(DP.Amount) AS Deposit 
 FROM CashUp_Deposit DP
 WHERE DP.No_Show != 1
 GROUP BY DP.Redeemed_Site_Lookup_Id
) DP
ON DP.Redeemed_Site_Lookup_Id = SL.Site_Lookup_Id LEFT JOIN
(SELECT I.Site_Lookup_Id, SUM(I.Amount) AS Income
 FROM CashUp_Income I
 GROUP BY I.Site_Lookup_Id
) I
ON I.Site_Lookup_Id = SL.Site_Lookup_Id LEFT JOIN
(SELECT P.Site_Lookup_Id, SUM(P.Amount) AS Payout
 FROM CashUp_Payout P
 GROUP BY I.Site_Lookup_Id
) P
ON P.Site_Lookup_Id = SL.Site_Lookup_Id 

然后调整查询的其余部分，以删除GROUP BY和SUM()的。