最新要闻

广告

手机

iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?

iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?

警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案

家电

当前热点-读SQL进阶教程笔记06_外连接

来源:博客园

1.SQL的弱点

1.1.SQL语句的执行结果转换为想要的格式


(资料图片仅供参考)

  • 1.1.1.格式转换

  • 1.1.2.SQL语言本来就不是为了这个目的而出现的

  • 1.1.3.SQL终究也只是主要用于查询数据的语言而已

1.2.生成报表的功能

  • 1.2.1.窗口函数

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

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

2.制作交叉表(行→列)

2.1.示例

  • 2.1.1.
  • 2.1.1.1.--水平展开求交叉表(1):使用外连接
SELECT C0.name,CASE WHEN C1.name IS NOT NULL THEN"○"ELSE NULL END AS "SQL入门",CASE WHEN C2.name IS NOT NULL THEN"○"ELSE NULL END AS "UNIX基础",CASE WHEN C3.name IS NOT NULL THEN"○"ELSE NULL END AS "Java中级"FROM (SELECT DISTINCT name FROM Courses) C0 --这里的C0是侧栏LEFT OUTER JOIN(SELECT name FROM Courses WHERE course = "SQL入门’) C1ON C0.name = C1.nameLEFT OUTER JOIN(SELECT name FROM Courses WHERE course = "UNIX基础’) C2ON C0.name = C2.nameLEFT OUTER JOIN(SELECT name FROM Courses WHERE course = "Java中级’) C3ON C0.name = C3.name;
  • 2.1.1.2.一般情况下,外连接都可以用标量子查询替代

    2.1.1.2.1.需要增加或者减少课程时,只修改SELECT子句即可,代码修改起来比较简单

    2.1.1.2.2.利于应对需求变更,对于需要动态生成SQL的系统也是很有好处的

    2.1.1.2.3.性能不太好

  • 2.1.1.3.--水平展开(2):使用标量子查询

SELECT C0.name,(SELECT "○"FROM Courses C1WHERE course = "SQL入门’AND C1.name = C0.name) AS "SQL入门",(SELECT "○"FROM Courses C2WHERE course = "UNIX基础’AND C2.name = C0.name) AS "UNIX基础",(SELECT "○"FROM Courses C3WHERE course = "Java中级’AND C3.name = C0.name) AS "Java中级"FROM (SELECT DISTINCT name FROM Courses) C0; --这里的C0是表侧栏
  • 2.1.1.4.嵌套使用CASE表达式

    2.1.1.4.1.CASE表达式可以写在SELECT子句里的聚合函数内部,也可以写在聚合函数外部

    2.1.1.4.2.其实在SELECT子句里,聚合函数的执行结果也是标量值,因此可以像常量和普通列一样使用

    2.1.1.4.3.和标量子查询的做法一样简洁,也能灵活地应对需求变更

  • 2.1.1.5.--水平展开(3):嵌套使用CASE表达式

SELECT name,CASE WHEN SUM(CASE WHEN course = "SQL入门’THEN 1 ELSE NULL END) = 1THEN"○"ELSE NULL END AS "SQL入门",CASE WHEN SUM(CASE WHEN course = "UNIX基础’THEN 1 ELSE NULL END) = 1THEN"○"ELSE NULL END AS "UNIX基础",CASE WHEN SUM(CASE WHEN course = "Java中级’THEN 1 ELSE NULL END) = 1THEN"○"ELSE NULL END AS "Java中级"FROM CoursesGROUP BY name;

3.汇总重复项于一列(列→行)

3.1.示例

  • 3.1.1.
  • 3.1.1.1.--列数据转换成行数据:使用UNION ALL
SELECT employee, child_1 AS child FROM PersonnelUNION ALLSELECT employee, child_2 AS child FROM PersonnelUNION ALLSELECT employee, child_3 AS child FROM Personnel;
  • 3.1.1.2. 视图
CREATE VIEW Children(child)AS SELECT child_1 FROM PersonnelUNIONSELECT child_2 FROM PersonnelUNIONSELECT child_3 FROM Personnel;child-----一郎二郎三郎春子夏子

3.1.1.2.1.--获取员工子女列表的SQL语句(没有孩子的员工也要输出)

SELECT EMP.employee, CHILDREN.childFROM Personnel EMPLEFT OUTER JOIN ChildrenON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);

4.制作嵌套式表侧栏

4.1.示例

  • 4.1.1.
  • 4.1.2.
  • 4.1.3.结果

    • 4.1.3.1.
  • 4.1.4.--使用外连接生成嵌套式表侧栏:错误的SQL语句
SELECT MASTER1.age_class AS age_class,MASTER2.sex_cd AS sex_cd,DATA.pop_tohoku AS pop_tohoku,DATA.pop_kanto AS pop_kantoFROM (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_kantoFROM TblPopGROUP BY age_class, sex_cd) DATARIGHT OUTER JOIN TblAge MASTER1--外连接1:和年龄层级主表进行外连接ON MASTER1.age_class = DATA.age_classRIGHT OUTER JOIN TblSex MASTER2--外连接2:和性别主表进行外连接ON MASTER2.sex_cd = DATA.sex_cd;
  • 4.1.4.1.--停在第1个外连接处时:结果里包含年龄层级为2的数据
SELECT MASTER1.age_class AS age_class,DATA.sex_cd AS sex_cd,DATA.pop_tohoku AS pop_tohoku,DATA.pop_kanto AS pop_kantoFROM (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_kantoFROM TblPopGROUP BY age_class, sex_cd) DATARIGHT OUTER JOIN TblAge MASTER1ON MASTER1.age_class = DATA.age_class;
  • 4.1.5.如果不允许进行两次外连接,那么调整成一次就可以了

  • 4.1.6.对于不支持CROSS JOIN语句的数据库,可以像FROM TblAge,TblSex这样不指定连接条件,把需要连接的表写在一起,其效果与交叉连接一样

  • 4.1.7.如果先生成主表的笛卡儿积再进行连接,很容易就可以完成

  • 4.1.8.--使用外连接生成嵌套式表侧栏:正确的SQL语句

SELECT MASTER.age_class AS age_class,MASTER.sex_cd AS sex_cd,DATA.pop_tohoku AS pop_tohoku,DATA.pop_kanto AS pop_kantoFROM (SELECT age_class, sex_cdFROM TblAge CROSS JOIN TblSex ) MASTER --使用交叉连接生成两张主表的笛卡儿积LEFT OUTER 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_kantoFROM TblPopGROUP BY age_class, sex_cd) DATAON MASTER.age_class = DATA.age_classAND MASTER.sex_cd = DATA.sex_cd;

5.作为乘法运算的连接

5.1.示例

  • 5.1.1.
  • 5.1.2.--解答(1):通过在连接前聚合来创建一对一的关系
SELECT I.item_no, SH.total_qtyFROM Items I LEFT OUTER JOIN(SELECT item_no, SUM(quantity) AS total_qtyFROM SalesHistoryGROUP BY item_no) SHON I.item_no = SH.item_no;
  • 5.1.2.1.以商品编号为主键的临时视图

  • 5.1.2.2.无法利用索引优化查询

  • 5.1.3.--解答(2):先进行一对多的连接再聚合

SELECT I.item_no, SUM(SH.quantity) AS total_qtyFROM Items I LEFT OUTER JOIN SalesHistory SHON I.item_no = SH.item_no 一对多的连接GROUP BY I.item_no;
  • 5.1.3.1.代码更简洁

  • 5.1.3.2.没有使用临时视图,所以性能也会有所改善

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

6.全外连接

6.1.FULL OUTER JOIN

6.2.相当于求集合的和(UNION,也称并集)

  • 6.2.1.内连接相当于求集合的积(INTERSECT,也称交集)

6.3.示例

  • 6.3.1.--全外连接保留全部信息
SELECT COALESCE(A.id, B.id) AS id,A.name AS A_name,B.name AS B_nameFROM Class_A A FULL OUTER JOIN Class_B BON A.id = B.id;
  • 6.3.1.1.--数据库不支持全外连接时的替代方案
SELECT A.id AS id, A.name, B.nameFROM Class_A A LEFT OUTER JOIN Class_B BON A.id = B.idUNIONSELECT B.id AS id, A.name, B.nameFROM Class_A A RIGHT OUTER JOIN Class_B BON A.id = B.id;

6.3.1.1.1.分别进行左外连接和右外连接,再把两个结果通过UNION合并起来

6.4.COALESCE是SQL的标准函数

  • 6.4.1.可以接受多个参数,功能是返回第一个非NULL的参数

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

7.用外连接求差集:B-A

7.1.示例

  • 7.1.1.
SELECT B.id AS id, B.name AS B_nameFROM Class_A A RIGHT OUTER JOIN Class_B BON A.id = B.idWHERE A.name IS NULL;

7.2.可以作为NOT IN和NOT EXISTS之外的另一种解法

7.3.可能是差集运算中效率最高的

8.用全外连接求异或集

8.1.SQL没有定义求异或集的运算符

8.2.用集合运算符

  • 8.2.1.(A UNION B) EXCEPT (A INTERSECT B)

  • 8.2.2.(A EXCEPT B) UNION (B EXCEPT A)

  • 8.2.3.性能开销大

8.3.示例

  • 8.3.1.
SELECT COALESCE(A.id, B.id) AS id,         COALESCE(A.name , B.name ) AS name     FROM Class_A  A  FULL OUTER JOIN Class_B  B       ON A.id = B.id    WHERE A.name IS NULL       OR B.name IS NULL;

9.用外连接进行关系除法

9.1.示例

  • 9.1.1.--用外连接进行关系除法运算:差集的应用
SELECT DISTINCT shopFROM ShopItems SI1WHERE NOT EXISTS(SELECT I.itemFROM Items I LEFT OUTER JOIN ShopItems SI2ON I.item = SI2.itemAND SI1.shop = SI2.shopWHERE SI2.item IS NULL) ;

关键词: