极客教程SQL 组合使用外连接查询与聚合函数，略微修改了 EMP_BONUS 表的数据，使得部门编号为 10 的员工中只有部分人获得了奖金。考虑如下所示的 EMP_BONUS 表和查询语句，该查询（表面上）计算出了部门编号为 10 的员工的工资总额和奖金总额。
SQL 组合使用外连接查询与聚合函数 问题描述
略微修改了 EMP_BONUS 表的数据，使得部门编号为 10 的员工中只有部分人获得了奖金。考虑如下所示的 EMP_BONUS 表和查询语句，该查询（表面上）计算出了部门编号为 10 的员工的工资总额和奖金总额。
select * from emp_bonus
EMPNO RECEIVED TYPE
---------- ----------- ----------
7934 17-MAR-2005 1
7934 15-FEB-2005 2
select deptno,
sum(sal) as total_sal,
sum(bonus) as total_bonus
from (
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal*case when eb.type = 1 then .1
when eb.type = 2 then .2
else .3 end as bonus
from emp e, emp_bonus eb
where e.empno = eb.empno
and e.deptno = 10
)
group by deptno
DEPTNO TOTAL_SAL TOTAL_BONUS
------ ---------- -----------
10 2600 390
奖金总额的结果是正确的，但工资总额却不是部门编号为 10 的员工的工资总额。下面的查询语句解释了为什么工资总额不正确。
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal*case when eb.type = 1 then .1
when eb.type = 2 then .2
else .3 end as bonus
from emp e, emp_bonus eb
where e.empno = eb.empno
and e.deptno = 10
EMPNO ENAME SAL DEPTNO BONUS
-------- ---------- ---------- ---------- ----------
7934 MILLER 1300 10 130
7934 MILLER 1300 10 260
上述查询没有计算部门编号为 10 的全部员工的工资总额，实际上只有 MILLER 的工资被计入总和，而且被错误地计算了两次。其实，你最终想得到如下所示的结果集。
DEPTNO TOTAL_SAL TOTAL_BONUS
------ ---------- -----------
10 8750 390
SQL 组合使用外连接查询与聚合函数 解决方案
下面的解决方案也和 3.9 节的类似，不同之处在于要外连接 EMP_BONUS 表，确保把部门编号为 10 的全部员工都包括进来。
DB2、MySQL、PostgreSQL 和 SQL Server
外连接 EMP_BONUS 表，然后去掉部门编号为 10 的员工的重复项，再计算工资总和。
1 select deptno,
2 sum(distinct sal) as total_sal,
3 sum(bonus) as total_bonus
4 from (
5 select e.empno,
6 e.ename,
7 e.sal,
8 e.deptno,
9 e.sal*case when eb.type is null then 0
10 when eb.type = 1 then .1
11 when eb.type = 2 then .2
12 else .3 end as bonus
13 from emp e left outer join emp_bonus eb
14 on (e.empno = eb.empno)
15 where e.deptno = 10
16 )
17 group by deptno
也可以使用窗口函数 SUM OVER。
1 select distinct deptno,total_sal,total_bonus
2 from (
3 select e.empno,
4 e.ename,
5 sum(distinct e.sal) over
6 (partition by e.deptno) as total_sal,
7 e.deptno,
8 sum(e.sal*case when eb.type is null then 0
9 when eb.type = 1 then .1
10 when eb.type = 2 then .2
11 else .3
12 end) over
13 (partition by deptno) as total_bonus
14 from emp e left outer join emp_bonus eb
15 on (e.empno = eb.empno)
16 where e.deptno = 10
17 ) x
Oracle
对于 Oracle 9i 数据库及其后续版本，上述解决方案仍然适用。除此之外，我们也可以使用 Oracle 专有的外连接语法。对于 Oracle 8i 数据库及更早的版本，只能使用该语法实现外连接。
1 select deptno,
2 sum(distinct sal) as total_sal,
3 sum(bonus) as total_bonus
4 from (
5 select e.empno,
6 e.ename,
7 e.sal,
8 e.deptno,
9 e.sal*case when eb.type is null then 0
10 when eb.type = 1 then .1
11 when eb.type = 2 then .2
12 else .3 end as bonus
13 from emp e, emp_bonus eb
14 where e.empno = eb.empno (+)
15 and e.deptno = 10
16 )
17 group by deptno
与 DB2 及其他数据库类似，Oracle 8i 数据库也支持 SUM OVER 语法，但必须把上面的查询语句里出现的外连接改为 Oracle 专有的语法。
SQL 组合使用外连接查询与聚合函数 扩展知识
本实例“问题”部分中的第二个查询语句连接了 EMP 表和 EMP_BONUS 表，却只返回了员工 MILLER 的两行数据，这是导致 EMP 表的工资总额计算出错的原因（部门编号为 10 的其他员工没有奖金，他们的工资没有被计入总和）。解决办法则是把 EMP 表外连接到 EMP_BONUS 表，这样一来，那些没有奖金的员工也会被计算进来。如果一个员工没有奖金，那么 EMP_BONUS 表中的 TYPE 就是 Null 值。注意到这一点非常重要，因为 CASE 语句部分已经在 3.9 节的基础上稍微有了变动。如果 EMP_BONUS 表中的 TYPE 为 Null 值，则 CASE 表达式会返回 0，这样就不会对总和产生影响。
下面的查询语句是另一种解决方案。首先计算部门编号为 10 的员工的工资总额，然后再连接 EMP 表和 EMP_BONUS 表（这样就避免了使用外连接）。下面的查询语句适用于所有的关系数据库管理系统。
select d.deptno,
d.total_sal,
sum(e.sal*case when eb.type = 1 then .1
when eb.type = 2 then .2
else .3 end) as total_bonus
from emp e,
emp_bonus eb,
(
select deptno, sum(sal) as total_sal
from emp
where deptno = 10
group by deptno
) d
where e.deptno = d.deptno
and e.empno = eb.empno
group by d.deptno,d.total_sal
DEPTNO TOTAL_SAL TOTAL_BONUS
--------- ---------- -----------
10 8750 390