SQL SELECT 对值求和，不包括重复项

Question

我在 Oracle SQL 中遇到一个问题，我正在尝试解决这个问题。

我将用一个例子来说明。我正在查询三个表:

Employees
__________________________________________
| EmployeeID | Name                      |
| 1          | John Smith                |
| 2          | Douglas Hoppalot          |
| 3          | Harry Holiday             |
... 



InternalCosts
________________________________
| IntID | Amount | EmployeeID  |
| 1     | 10     |     1       |
| 2     | 20     |     2       |
| 3     | 30     |     1       |
...


ExternalCosts
________________________________
| ExtID | Amount | EmployeeID  |
| 1     | 40     |     1       |
| 2     | 50     |     2       |
| 3     | 60     |     1       |
...

我想要实现的是每个员工一行的结果，以及他们每个内部和外部成本的总和，即

____________________________________________________________
| Name             | InternalCostTotal | ExternalCostTotal |
| John Smith       | 40                | 100               |
| Douglas Hoppalot | 20                | 50                |
...

我遇到的问题是，当我查询InternalCosts 和ExternalCosts 表时，我得到每种成本的排列，而不仅仅是每个员工的一种。当我按员工姓名分组并对金额字段求和时，值太高。我尝试过的:

SELECT emp.Name, sum(int.Amount), sum(ext.Amount) 
FROM Employees emp,
     InternalCosts int,
     ExternalCosts ext
WHERE emp.EmployeeId = int.EmployeeID
  and emp.EmployeeID = ext.EmployeeID
GROUP BY emp.Name

上面的示例将返回:

 ____________________________________________________________
 | Name             | InternalCostTotal | ExternalCostTotal |
 | John Smith       | 80                | 200               | <- too high!
 | Douglas Hoppalot | 20                | 50                |
 ...

感谢任何帮助/建议/想法!

Answer 1

您应该对 int 和 ext 使用子查询来求和，然后加入到子查询中。

我还建议使用显式联接而不是表、表、表

例如

SELECT emp.Name, int.Amount AS InternalCostTotal, ext.Amount AS ExternalCostTotal
FROM Employees emp
JOIN ( 
    SELECT EmployeeID, SUM(Amount) AS Amount 
    FROM InternalCosts 
    GROUP BY EmployeeID 
) int ON emp.EmployeeId = int.EmployeeID
JOIN ( 
    SELECT EmployeeID, SUM(Amount) AS Amount 
    FROM ExternalCosts 
    GROUP BY EmployeeID 
) ext ON emp.EmployeeId = ext.EmployeeID