从多个表中获取和

小编典典

从多个表中获取和

sql

我在单个SQL语句中从多个表中提取Sums时遇到问题。

我有三个表tblCases，tblTimesheetEntries和tblInvoices在tblCases和其他两个表之间存在一对多的关系。

我目前正在使用以下SQL语句

SELECT c.CaseNo, SUM(i.InvFees), SUM(t.Fees)
FROM tblCases AS c
INNER JOIN tblInvoices AS i ON c.CaseNo = i.CaseNo
INNER JOIN tblTimesheetEntries AS t ON c.CaseNo = t.CaseNo
GROUP BY c.CaseNo
ORDER BY c.CaseNo;

但是，这似乎重复了发票金额。例如，如果一个案例只有一张发票，但是说有4个时间表条目，则它将计算出发票金额的4倍作为该表的总和。

如果我将分组取出，然后运行以下SQL：

SELECT c.CaseNo, i.InvFees, t.Fees
FROM tblCases AS c
INNER JOIN tblInvoices AS i ON c.CaseNo = i.CaseNo
INNER JOIN tblTimesheetEntries AS t ON c.CaseNo = t.CaseNo    
ORDER BY c.CaseNo;

我看到发生这种情况是因为发票金额在所有4行中都重复了，例如

Case 1001,  Inv 001  950.00,  TimeFees  250.00
Case 1001,  Inv 001  950.00,  TimeFees  175.00
Case 1001,  Inv 001  950.00,  TimeFees  225.00
Case 1001,  Inv 001  950.00,  TimeFees  190.00

因此，发票总数是发票001金额的四倍。

我想从上述数据中返回的是一条求和线：

Case 1001,  Total Invoices 950.00,  Total TimeFees 840.00

如何避免求和中的重复？

阅读 212

2021-04-28

共1个答案

小编典典

SELECT c.CaseNo,
       i.InvFees,
       t.Fees
FROM   tblCases AS c
       INNER JOIN (SELECT CaseNo,
                          Sum(InvFees) AS InvFees
                   FROM   tblInvoices
                   GROUP  BY CaseNo) AS i
         ON c.CaseNo = i.CaseNo
       INNER JOIN (SELECT CaseNo,
                          Sum(Fees) AS Fees
                   FROM   tblTimesheetEntries
                   GROUP  BY CaseNo) AS t
         ON c.CaseNo = t.CaseNo
ORDER  BY c.CaseNo;

2021-04-28