SQL 组合使用外连接查询与聚合函数|极客教程

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