SQLCookBook第三章学习日记10

3.10聚集与外联接

修改emp_bonus表，使得在部门10中并不是每个员工都有奖金。

select deptno ,    sum(sal) as total_sal,    sum(bonus) as total_bonus    from (    select e.empno,            e.name,            e.sal,            e.deptno,            e.sal*case when eb.type = 1 then .1                        when eb.tyoe = 2 then .2                        else .3 end as bonus    from emp e, emp_bonus ed        where e.empno = eb.empno            and e.deptno = 10)group by deptno

total_bonus的结果是正确的，但是total_sal的值却不能体现在部门10中所有员工的工资。下面的查询课揭示total_sal不正确的原因：

select e.empno,        e.ename,        e.sal,        e.deptno,        e.sal*case when eb.type = 1 then .1                    when eb.type = 2 thrn .2                else .e end as bonus    from emp e ,emp_bonus ebwhere e.empno = eb.empno    and e.deptno = 10

与其说是汇总了部门10中所有员工的工资，不如说是只汇总了‘miller’ 一个人的工资，而且还被错误的汇总了两次。

解决方案：

此问题的解决方案与3.9中的解决方案类似，但是这里应当对应表emp_bonus使用外联接来确保部门10中所有员工都包含到结果中。

DB2、MySQL、PostgreSQL和 SQL Server
外联结到表emp_bonus，然后只对部门10中不同的工资进行汇总：

select deptno,    sum(distinct 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 is null then 0                            when eb.type = 1 then .1                            when eb.type = 2 then .2                            else .3 end as bonus        from emp e left outer join emp_bonus eb            on (e.deptno = eb.deptno)        where e.deptno = 10             )    group by deptno

也可以使用窗口函数 sum over:

select distinct deptno,total_sal,total_bonus    from (        select e.empno,            e.ename,            sum(distinct e.sal) over            (partition by e.deptno) as total_sak,            e.deptno,            sum(e.sal*case when eb.type is null then 0                        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 left outer join emp_bonus eb                on (e.empno = eb.empno)        where e.deptno = 10    )x

Oracle
如果使用Oracle9i Database及以后的版本，可以使用上述的解决方案。另一种方案是使用Oracle特有的外联结语法，如果使用Oracle8i及以前的版本，则只能使用这种语法。

select deptno        sum(distinct 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 is null then 0                            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

Oracle 8i Database 用户也可以使用DB2和其他数据库的sum over语法，但是必须像前一查询一样修改为Oracle持有的外联接语法。

讨论：

在问题部分的第二个查询中，对表emp和emp_bonus作连接，结果只返回了员工”miller”的行，这就是导致对emp.sal求和错误地原因（deptno 10的其他员工没有奖金，他们的工资也没有计入工资总额中）该解决方案将表emp外联结到表emp_bonus使得每个没有奖金的员工也包括在结果中。如果每个员工没有奖金，emp_bonus.type将返回null，一定要记住，这里对case语句作了修改，跟3.9节中方案不同，如果emp_bonux.type为NULL，case表达式返回0，对汇总结果没有影响。

下面的查询是另一种解决方案。首先计算部门10的工资总额，然后联接到表emp，最后再联接到emp_bonus（避免了外联结）下面的查询适用于所有DBMS。

select d.deptno,        d.total_sal,        sum(e.sal*case when eb.type = 1 then .1                        when eb.type = 2 then .2                        else .e end ) as total_bonusfrom 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