SQLCookBook第三章学习日记9

3.8识别和消除笛卡尔积

问题：
要返回在部门10中每个员工的姓名，以及部门的工作地点，下面的查询达到的是错误数据：

selelct e.ename,d.loc
    from emp e ,dept d
where e.deptno =10

解决方案:在from子句对表进行连接来返回正确的结果集：

select e.ename,d.loc
    from emp e,dept d
    where e.deptno =10
        and d.deptno = e.emptno

讨论：
看表dept中的数据，可以看出，部门10的工作地点是在New York，所以，在返回值中部门所在地点除了New york以外的任何值都是错误的。错误查询得到的行数是from子句后面两个表基数的积。在原查询中，对表emp的筛选条件是部门为10，结果有3行，因为没有对表dept进行筛选，表dept的所有四行全部返回，3乘以4得12，所以这个错误查询就返回了12行。一般来说，要避免产生笛卡尔积，需要使用n-1规则，这里的n为from子句中表的数量，并且n-1是要避免产生笛卡尔积的最小连接数。根据在表中的关键字和链接列不同，可能需要超过n-1个连接，但是对党写查询来说，n-1是一个好的开始。

注意：如果笛卡尔积应用适当也很有用。很多查询都用到了笛卡尔积，常用的场合有转置（反向转置）结果集，产生顺序值和模拟循环等

3.9聚集和联接

问题:要在包含多个表的查询中执行聚集运算,要确保表间连接不能使聚集运算发生错误.例如,要查找在部门10中所有员工的工资合计和奖金合计.由于有些员工的奖金激励不只一条,在表emp和表emp_bonus之间做连接会导致聚集函数sum算得的值错误.

现在，考虑一下下面的返回的在部门10中所有员工的工资和奖金的查询。表bonus中的type字段决定奖金额，类型1的奖金为员工工资的10%，类型2为20%，类型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

进行到这，一切正常，然而为了计算奖金总数而跟表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

尽管total_bonus所返回的值是正确的，total_sal却是错误的。

total _sal为什么错了，因为联接导致sal列存在重复。考虑下面的查询。该查询联接表emp和emp_bonus

select e.ename,e.sal
    from emp e,emp_bonus eb
        where e.empno = eb.empno
            and e.deptno =10

现在可以很容易的看出total_sal为什么错了，因为miller的工资被统计了两次。

解决方案：当处理聚集与联接混合操作时，一定要小心。如果联接产生重复行，可以有两种方法来避免聚集函数计算错误，方法之一，只要在调用聚集函数时使用关键字distinct，这样每个值只参与计算一次，另一种方法是，在进行连接前先只想聚集操作（在内联视图中），这样，因为聚集计算已经在连接前完成了，所以就可以避免聚集函数计算错误，从而可以完全避免产生此问题。下面列出的解决方案使用了distinct关键字，而”讨论”部分将讨论如何在联结前使用内联视图来只想聚集操作。

MySQL和PostgreSQL
使用Distinct开关键字只对不相同的工资求和;

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 = 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

DB2,Oracle 和SQL Server
这些平台也支持上面的解决方案，此外他们还支持另一种使用窗口函数 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_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
        )x

讨论：
MySQL和PostgreSQL
在问题部分的第二个查询在连接表emp和emp_bonus时，对员工“Miller”产生了两条记录，这就是导致计算emp.sal的和出错的原因（其工资加了两次）。解决方案是只把不同的emp.sal值相加。下面的查询是另外一种解决方案。首先计算部门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 .3 end) as total_bonus
    from emp e,emp_bonus eb,
        (
        select deptno,sum(sal) sass 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           

DB2、Oracle 和SQL Server
另一种解决方案发挥了窗口函数sum over的优势。

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 .e end) over
        (partition by deptno) as total_bonus
        from emp e,emp_bonus eb
        where e.empno = eb.empno
            and e.deptno = 10

在上述查询中，sum over窗口函数被两次调用，第一次用来计算给定分区或组中不同工资合计，本例中，分区为deptno为10，部门10不相同工资的总额为8750；第二次调用sum over 用来计算同一分区的奖金合计。取total_sal dept_no 和total_bonus 的唯一值就得到最终结果集。