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

SQL 组合使用连接查询与聚合函数，你想执行一个聚合操作，但查询语句涉及多个表。你希望确保表之间的连接查询不会干扰聚合操作。例如，你希望计算部门编号为 10 的员工的工资总额以及奖金总和。因为有部分员工多次获得奖金，所以在 EMP 表和 EMP_BONUS 表连接之后再执行聚合函数 SUM，就会得出错误的计算结果。在这个问题中，EMP_BONUS 表里有如下数据。

SQL 组合使用连接查询与聚合函数问题描述

你想执行一个聚合操作，但查询语句涉及多个表。你希望确保表之间的连接查询不会干扰聚合操作。例如，你希望计算部门编号为 10 的员工的工资总额以及奖金总和。因为有部分员工多次获得奖金，所以在 EMP 表和 EMP_BONUS 表连接之后再执行聚合函数 SUM，就会得出错误的计算结果。在这个问题中，EMP_BONUS 表里有如下数据。

select * from emp_bonus
　
EMPNO RECEIVED          TYPE
----- ----------- ----------
 7934 17-MAR-2005          1
 7934 15-FEB-2005          2
 7839 15-FEB-2005          3
 7782 15-FEB-2005          1

现在，考虑下面的查询语句，它返回了部门编号为 10 的所有员工的工资和奖金。BONUS 表中的 TYPE 列决定了奖金的数额。若 TYPE 值等于 1，则奖金为工资的 10%；若 TYPE 值等于 2，则奖金为工资的 20%；若 TYPE 值等于 3，则奖金为工资的 30%。

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
    7839 KING             5000         10        500
    7782 CLARK            2450         10        245

到目前为止，一切都很顺利。然而，如果你试图连接 EMP_BONUS 表并计算奖金总和，就会出错。

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
       )x
 group by deptno
　
DEPTNO  TOTAL_SAL TOTAL_BONUS
------ ---------- -----------
    10      10050        2135

尽管奖金总额（TOTAL_BONUS）是正确的，但工资总额（TOTAL_SAL）却是错误的。部门编号为 10 的所有员工的工资总额应该是 8750，如下所示。

select sum(sal) from emp where deptno=10
　
  SUM(SAL)
----------
      8750

为什么工资总额不对呢？这是因为连接查询导致某些行的 SAL 列出现了两次。考虑下面连接 EMP 表和 EMP_BONUS 表的查询语句。

select e.ename,
       e.sal
  from emp e, emp_bonus eb
 where e.empno = eb.empno
   and e.deptno = 10
　
    ENAME             SAL
    ---------- ----------
    CLARK            2450
    KING             5000
    MILLER           1300
    MILLER           1300

现在就能很容易地看出来为什么工资总额是错误的了，因为 MILLER 的工资被统计了两次。你真正想要的结果集应该如下所示。

DEPTNO  TOTAL_SAL TOTAL_BONUS
------ ---------- -----------
    10       8750        2135

SQL 组合使用连接查询与聚合函数解决方案

在连接查询里进行聚合运算时，必须十分小心才行。如果连接查询产生了重复行，通常有两种办法来使用聚合函数，而且可以避免得出错误的计算结果。一种方法是，调用聚合函数时直接使用关键字 DISTINCT，这样每个值都会先去掉重复项再参与计算；另一种方法是，在进行连接查询之前先执行聚合运算（以内嵌视图的方式），这样可以避免错误的结果，因为聚合运算发生在连接查询之前。下面的解决方案使用了 DISTINCT。之后，我们将讨论在连接查询之前使用内嵌视图执行聚合运算的做法。
MySQL 和 PostgreSQL
使用 DISTINCT 计算工资总额。

 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 = 1 then .1
10                    when eb.type = 2 then .2
11                    else .3
12               end  as bonus
13    from emp e, emp_bonus eb
14   where e.empno  = eb.empno
15     and e.deptno = 10
16         ) x
17   group by deptno

DB2、Oracle 和 SQL Server
上述解决方案也适用于这些数据库。另外，它们还支持窗口函数 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 = 1 then .1
 9                        when eb.type = 2 then .2
10                        else .3 end) over
11         (partition by deptno) as total_bonus
12   from emp e, emp_bonus eb
13  where e.empno  = eb.empno
14    and e.deptno = 10
15        ) x

SQL 组合使用连接查询与聚合函数扩展知识

本实例“问题”部分的第二个查询语句把 EMP 表和 EMP_BONUS 表连接起来，并返回了员工 MILLER 的两行数据，这是导致 EMP 表的工资总额出错的原因（MILLER 的工资被加了两次）。对应的解决办法是只计算不同的 EMP.SAL 值。下面的查询语句是另一种解决方案。首先计算部门编号为 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         2135

DB2、Oracle 和 SQL Server
上面的另一种解决方案利用了窗口函数 SUM OVER。下面的查询语句来自该解决方案的第 3 行至第 14 行，返回的结果集如下。

select e.empno,
       e.ename,
       sum(distinct e.sal) over
       (partition by e.deptno) as total_sal,
       e.deptno,
       sum(e.sal*case when eb.type = 1 then .1
                      when eb.type = 2 then .2
                      else .3 end) over
       (partition by deptno) as total_bonus
  from emp e, emp_bonus eb
 where e.empno = eb.empno
   and e.deptno = 10
　
EMPNO ENAME       TOTAL_SAL DEPTNO TOTAL_BONUS
----- ---------- ---------- ------ -----------
 7934 MILLER           8750     10        2135
 7934 MILLER           8750     10        2135
 7782 CLARK            8750     10        2135
 7839 KING             8750     10        2135

窗口函数 SUM OVER 被调用了两次，第一次调用针对指定的分区或者分组计算工资总额。在本例中，分区指的是编号为 10 的部门，该部门员工的工资总额是 8750。第二次调用 SUM OVER 针对同一个分区计算奖金总额。最终的结果集则是在去除了 TOTAL_SAL、DEPTNO 以及 TOTAL_BONUS 组合的重复项之后产生的。