SQL SELECT Sum值，不包括重复项 [英] SQL SELECT Sum values without including duplicates

问题描述

我在Oracle SQL中有一个问题正在设法解决.

I have a problem in Oracle SQL that I'm trying to get my head around.

我将举例说明.我要查询三个表:

I'll illustrate with an example. I have three tables that I am querying:

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       |
...

我想要实现的结果是每位员工一行，每个员工的内部和外部成本之和，即

What I want to achieve is a result of one row per employee, with sums of each of their internal and external costs, i.e.

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

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

The problem I have is that when I query both the InternalCosts and ExternalCosts tables, I get each permutation of costs, not just one per employee. When I group by employee Name and sum the amount fields the values are too high. What I have tried:

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                |
 ...

感谢您的任何帮助/建议/想法！

Grateful for any help/advice/thoughts!

推荐答案

您应该在int和ext上使用子查询进行求和，然后加入子查询.

You should use subqueries on int and ext to do the summing, and join to the subqueries.

我还建议使用显式JOIN，而不要使用表，表，表

I also suggest using explicit JOINs rather than table, table, table

例如

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

这篇关于SQL SELECT Sum值，不包括重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！