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 eb
where 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_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