SQL进阶教程外连接小结和练习题

　　1.SQL不是用来生成报表的语言，所以不建议用它来进行格式转换；

　　2.必要时考虑用外连接或者CASE表达式来解决问题；

　　3.生成嵌套式表侧栏时，如果先生成主表的笛卡尔积再进行连接，很容易就可以完成；

　　4.从行数来看，表连接可以看成乘法。因此，当表之间是一对多的关系时，连接后行数不会增加；

　　5.外连接的思想和集合运算很像，使用外连接可以实现各种集合运算。

 

　　练习题

　　1.先连接还是先聚合

　　在“交叉表里制作嵌套式表侧栏”部分里，我们通过聚合将DATA视图和MASTER视图转换为一对一的关系之后进行了连接操作。采用这种做法时，代码的确比较好理解，但是这就需要创建两个临时视图，性能并不是很好。请想办法改善一下代码，尽量减少临时视图。　　

　　

SELECT MASTER.age_range AS '年龄层级',
       MASTER.sex AS '性别',
       SUM(CASE WHEN pref_name IN ('秋田','青森') THEN population ELSE NULL END) AS pop_tohoku,
       SUM(CASE WHEN pref_name IN ('东京','千叶') THEN population ELSE NULL END) AS pop_kanto
FROM (SELECT age_class, age_range, sex, sex_cd
            FROM TblAge CROSS JOIN TblSex ) MASTER 
LEFT JOIN tblpop
ON MASTER.age_class = tblpop.age_class AND MASTER.sex_cd = tblpop.sex_cd
GROUP BY age_range, sex

UNION

SELECT MASTER.age_range AS '年龄层级',
       MASTER.sex AS '性别',
             SUM(CASE WHEN pref_name IN ('秋田','青森') THEN population ELSE NULL END) AS pop_tohoku,
       SUM(CASE WHEN pref_name IN ('东京','千叶') THEN population ELSE NULL END) AS pop_kanto
FROM (SELECT age_class, age_range, sex, sex_cd
            FROM TblAge CROSS JOIN TblSex ) MASTER 
RIGHT JOIN tblpop
ON MASTER.age_class = tblpop.age_class AND MASTER.sex_cd = tblpop.sex_cd
GROUP BY age_range, sex

原方法是这样的：

SELECT MASTER.age_range AS '年龄层级',
       MASTER.sex AS '性别',
       DATA.pop_tohoku AS '东北',
       DATA.pop_kanto AS '关东'
FROM (SELECT age_class, age_range, sex, sex_cd
            FROM TblAge CROSS JOIN TblSex ) MASTER 
LEFT JOIN  (SELECT age_class, sex_cd,
            SUM(CASE WHEN pref_name IN ('秋田','青森') THEN population ELSE NULL END) AS pop_tohoku,
            SUM(CASE WHEN pref_name IN ('东京','千叶') THEN population ELSE NULL END) AS pop_kanto
            FROM tblpop
            GROUP BY age_class, sex_cd) DATA
ON MASTER.age_class = DATA.age_class AND MASTER.sex_cd = DATA.sex_cd;

　　视图MASTER 和DATA中行的关系时一对一的关系：

　　MASTER

　　

　　DATA

　　

　　而实际上MASTER与 tblpop表中的内容是一对多的关系，按照本节中：表连接可以看成行的乘法的观点，可以直接将master与tblpop进行全外连接：

　　由于Mysql不支持 FULL OUTER JOIN，所以采用 左外连接 union 右外连接的方法进行拼接。

 　　在本题中我将先聚合后连接改成了先连接后聚合，到底应该先采用哪种方法呢？

 

　　二、请留意孩子的人数

　　在“用外连接进行行列转换（列—>行）：汇总重复列于一列”部分，我们求得了以员工为单位的员工子女列表。有了这几个列表之后，对员工进行一下聚合很容易就可以知道每个员工抚养了几个孩子。

　　期待输出的结果如下所示：

　　

SELECT EMP.employee, COUNT(CHILDREN.child) AS child_cnt
FROM personnel EMP LEFT JOIN CHILDREN 
ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3)
GROUP BY employee
ORDER BY child_cnt DESC

 

 　　三、全外连接和MERGE运算符

　　MERGE运算符是在SQL：2003标准中引入的新特性，因为它可以将两张表的信息汇总到一张表上，所以在需要将分散在多个数据源的数据汇总到一起的场景中能发挥很强大的威力。

　　使用如下两张表：

　　

 

　　现在需要将class_b中的数据汇总到class_a里。处理逻辑是在表Class_A中查询表Class_B里的“id”列，如果存在则更新名字，如果不存在则插入。因此，两张表中同名的1号“田中”，以及表Class_B中不存在的3号“伊集院”没有变化，两张表中编号相同名字却不同的2号“铃木”被更新成“内海”，表Class_A中不存在的新同学“西园寺”被添加进表中。

 　　MERGE INTO 的用法，MySQL中没有MERGE_INTO可以使用：

　　

　　MERGE语句主要分为三块，第一块指定合并的表和匹配的列，即代码中的A快，ON（A.id = B.id）是匹配条件。

　　然后对每条记录进行匹配，并根据是否匹配到进行分支处理，本例中，对匹配到的记录执行 update语句，对没有匹配到的记录执行INSERT语句，执行结果之后会得到 A+B这样存储了完整信息的表。

　　在MySQL中如何实现？？