SQLCookBook第三章学习日记9
来源:互联网 发布:小米笔记本office软件 编辑:程序博客网 时间:2024/06/02 21:28
3.8识别和消除笛卡尔积
问题:
要返回在部门10中每个员工的姓名,以及部门的工作地点,下面的查询达到的是错误数据:
selelct e.ename,d.loc from emp e ,dept dwhere 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 的唯一值就得到最终结果集。
- SQLCookBook第三章学习日记9
- SQLCookBook第三章学习日记5
- SQLCookBook第三章学习日记6
- SQLCookBook第三章学习日记7
- SQLCookBook第三章学习日记8
- SQLCookBook第三章学习日记10
- SQLCookBook第二章学习日记3
- SQLCookBook第二章学习日记4
- SQLCookBook第四章学习日记13
- SQLCookBook第四章学习日记14
- SQLCookBook第一章学习日记1
- SQLCookBook第一章学习日记2
- SQLCookbook 学习笔记
- SQLCookbook 学习笔记 前言
- 学习日记第三篇
- SQLCookbook 学习笔记 6 字符串
- 跟我一起学习MySQL技术内幕(第五版):(第三章学习日记9)
- Delphi学习日记------第三课
- C#基础(一)字符串String
- 【Codeforces725F】Family Photos {贪心}
- 猫都能学会的Unity3D Shader入门指南(二)
- easyUI的databox取值
- 【转载】通过 FUNCTION NET_DUE_DATE_GET 得到MIRO付款日期(DUE ON DATE)
- SQLCookBook第三章学习日记9
- 回顾一下模板
- linux du命令参数及用法详解---linux统计磁盘空间大小命令
- Vijos P1056 图形面积
- Unity3d 基于NGUI的虚拟摇杆实现
- PHP命名空间的名称解析规则
- ZooKeeper
- AVAudioPlayer实现音乐播放+歌词与播放进度同步
- maven报错解决方案集