赞
踩
利用这张表生成下面这样格式的表格:
- SELECT C0.NAME,
- CASE WHEN C1.NAME IS NOT NULL THEN 'o' ELSE NULL END AS 'sql入门',
- CASE WHEN C2.NAME IS NOT NULL THEN 'o' ELSE NULL END AS 'UNIX基础',
- CASE WHEN C3.NAME IS NOT NULL THEN 'o' ELSE NULL END AS 'Java入门'
- FROM (SELECT DISTINCT name FROM Course) C0
- LEFT JOIN (SELECT DISTINCT name FROM COURSE WHERE COURSE = 'SQL入门') C1 ON C0.name = C1.name
- LEFT JOIN (SELECT DISTINCT name FROM COURSE WHERE COURSE = 'UNIX基础') C2 ON C0.name = C2.name
- LEFT JOIN (SELECT DISTINCT name FROM COURSE WHERE COURSE = 'Java入门') C3 ON C0.name = C3.name
-分析思路:侧栏我们可以使用主表来生成,其他显示成绩的可以使用外连接和CASE表达式, 可以理解通过主表生成4个C0~C3四个子集来完成此次操作。
C0:包含了所有的学生,起到一个学生主表作用,
C1~C3:每个表都是一个课程的集合,然后分别用外连接操作,通过学生来进行关联课程的3个集合。(注意外连接当为右边为NULL时右边显示NULL)。如果某位 学生学习过某个课程,则相应的课程列会出现他的姓名,否则为 NULL 。最后通过CASE表达式将课程列中员工的姓名转为O即可。
存在问题:当课程很多时我们再次使用这个结构的SQL,会非常的冗余,代码重复太高。
水平展开1:关联子查询
- SELECT c0.name,
- (select 'o' from course c1 where c1.name = c0.name and c1.course = 'SQL入门') as 'sql入门',
- (select 'o' from course c2 where c2.name = c0.name and c2.course = 'UNIX基础') as 'UNIX基础',
- (select 'o' from course c3 where c3.name = c0.name and c3.course = 'Java入门') as 'java'
- FROM (SELECT DISTINCT name FROM Course) C0
分析思路:使用主表生成表头,使用关联子查询来进行条件判定生成结果课程内容。
存在问题:虽然SQL结构简单了而且增加或者删除也方便,增加PHP课程著需要增加SELECT子查询即可。但是这样的性能并不好。
水平展开2:嵌套使用CASE表达式
- SELECT NAME,
- CASE WHEN SUM( CASE WHEN COURSE = 'sql入门' THEN 1 ELSE 0 END) = 1 THEN '0' ELSE NULL END AS 'SQL',
- CASE WHEN SUM( CASE WHEN COURSE = 'UNIX基础' THEN 1 ELSE 0 END) = 1 THEN '0' ELSE NULL END AS 'JAVA',
- CASE WHEN SUM( CASE WHEN COURSE = 'Java入门' THEN 1 ELSE 0 END) = 1 THEN '0' ELSE NULL END AS 'UNIX'
- from course group by name;
思路分析 :CASE 表达式可 以写在 SELECT 子句里的聚合函数内部,也可以写在聚合函数外部 (请参考 1-1 节)。这里,我们先把 SUM 函数的结果处理成 1 或者 NULL ,然后在外层的 CASE 表达式里将 1 转换成○。如果不使用聚合,那么返回结果的行数会是表 Courses 的行数,所以 这里以参加培训课程的员工为单位进行聚合。这种做法和标量子查询 的做法一样简洁,也能灵活地应对需求变更。关于将聚合函数的返回 值用于条件判断的写法,如果大家不习惯,可能会有点疑惑。但是, 其实在 SELECT 子句里,聚合函数的执行结果也是标量值,因此可以 像常量和普通列一样使用。如果明白这点,就不难理解了。
假设一个人有几个小孩那么我们如何将其提取为下图这种格式呢?、
如果转为上图数据格式我们可以借助union all来完成此次数据格式;
- select employee,child1 from perssonnel
- union all
- select employee,child2 from perssonnel
- union all
- select employee,child3 from perssonnel
问题:UNION ALL 不会排除重复的行,即使吉田没有孩子也会将其列出来,所以最好将其排除。
- -- 当然我们优先讲解外连接
- -- 如果我们有一个孩子名称的备用表,那么可以直接使用
- -- 孩子表为Child
- select per.employee,c.child
- from perssonnel per left join child c
- on c.child in (per.child1,per.child2,per.child3)
-
-
- -- 可以增加WHERE 条件
- select employee,child1 from perssonnel where child1 is not null
- union all
- select employee,child2 from perssonnel where child2 is not null
- union all
- select employee,child3 from perssonnel where child3 is not null;
思路分析:一般来说公司这种情况都有码表,通过外连接,但是ON条件是IN来指定所以当存在码表中就会返回名字,否则返回NULL。
三,作为乘法连接运算
假设让以商品为单位汇总各自的销量,请看下图:
如何使用外连接来形成这种数据格式?
- -- 大家可能第一时间想到这中方式
- -- 先连接在聚合
- select i.item ,sum(s.number)
- from item i left join shopitems s
- on i.item = s.item
- group by i.item
-
-
- -- 也可以使用先子查询在连接
- select i.item ,s.number
- from item i left join (select item ,sum(number) as number from shopitems GROUP BY item)s
- on i.item = s.item
思路分析:第一种方式我们是先连接在使用聚合函数搭配分组共同使用,第二种连接先使用子查询查询出结果在进行连接。两种方式其实都可以,只是第一种没有视图更加简单。
练习题
1.求每个员工抚养的孩子的人数
- select s.employee,count(s.child)
- from(
- select employee,child1 as child from perssonnel
- union all
- select employee,child2 as child from perssonnel
- union all
- select employee,child3 as child from perssonnel
-
- ) s group by s.employee
划重点:
- 1. SQL 不是用来生成报表的语言,所以不建议用它来进行格式转 换。
- 02. 必要时考虑用外连接或 CASE 表达式来解决问题。
- 03. 生成嵌套式表侧栏时,如果先生成主表的笛卡儿积再进行连接, 很容易就可以完成。
- 04. 从行数来看,表连接可以看成乘法。因此,当表之间是一对多的 关系时,连接后行数不会增加。
- 05. 外连接的思想和集合运算很像,使用外连接可以实现各种集合运
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。