SQL-外连接的用法（部分）

用外连接进行行列转换（行→列）

courses表：

CREATE TABLE Courses
(name   VARCHAR(32), 
 course VARCHAR(32), 
 PRIMARY KEY(name, course));

INSERT INTO Courses VALUES('赤井', 'SQL入门');
INSERT INTO Courses VALUES('赤井', 'UNIX基础');
INSERT INTO Courses VALUES('铃木', 'SQL入门');
INSERT INTO Courses VALUES('工藤', 'SQL入门');
INSERT INTO Courses VALUES('工藤', 'Java中级');
INSERT INTO Courses VALUES('吉田', 'UNIX基础');
INSERT INTO Courses VALUES('渡边', 'SQL入门');

 

需转换成下表：

 

 

 

方法及分析如下：

/* 水平展开求交叉表（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
    LEFT OUTER JOIN
    (SELECT name FROM Courses WHERE course = 'SQL入门' ) C1
    ON  C0.name = C1.name
      LEFT OUTER JOIN
        (SELECT name FROM Courses WHERE course = 'UNIX基础' ) C2
        ON  C0.name = C2.name
          LEFT OUTER JOIN
            (SELECT name FROM Courses WHERE course = 'Java中级' ) C3
            ON  C0.name = C3.name;
                        
/* 水平展开（2）：使用标量子查询 
易于扩展，增加PHP列时，只需要增加： 
(SELECT '○'
     FROM Courses C4
    WHERE course = 'PHP入门'
      AND C4.name = C0.name) AS "PHP入门"
            缺点是在select语句中使用标量子查询（或关联子查询）时，开销相当大
*/
SELECT  C0.name,
  (SELECT '○'
     FROM Courses C1
    WHERE course = 'SQL入门'
      AND C1.name = C0.name) AS "SQL入门",
  (SELECT '○'
     FROM Courses C2
    WHERE course = 'UNIX基础'
      AND C2.name = C0.name) AS "UNIX基础",
  (SELECT '○'
     FROM Courses C3
    WHERE course = 'Java中级'
      AND C3.name = C0.name) AS "Java中级"
  FROM (SELECT DISTINCT name FROM Courses) C0;
    
    /* 水平展开（3）：嵌套使用CASE表达式 
    聚合函数：SUM/MAX/MIN/AVG/COUNT
    聚合函数的执行结果也是标量值，因此可以像常量和普通列一样使用
    这里将聚合函数的返回值作为CASE表达式的判断条件
    */
SELECT  name,
        CASE WHEN SUM(CASE WHEN course = 'SQL入门' THEN 1 ELSE NULL END) >= 1
             THEN '○' ELSE NULL END AS "SQL入门",
        CASE WHEN SUM(CASE WHEN course = 'UNIX基础' THEN 1 ELSE NULL END) >= 1
             THEN '○' ELSE NULL END AS "UNIX基础",
        CASE WHEN SUM(CASE WHEN course = 'Java中级' THEN 1 ELSE NULL END) >= 1
             THEN '○' ELSE NULL END AS "Java中级"
  FROM Courses
 GROUP BY name;
 

 

用外连接进行行列转换（列→行）

 Personnel表：

 

 

 

CREATE TABLE Personnel
 (employee   varchar(32), 
  child_1    varchar(32), 
  child_2    varchar(32), 
  child_3    varchar(32), 
  PRIMARY KEY(employee));

INSERT INTO Personnel VALUES('赤井', '一郎', '二郎', '三郎');
INSERT INTO Personnel VALUES('工藤', '春子', '夏子', NULL);
INSERT INTO Personnel VALUES('铃木', '夏子', NULL,   NULL);
INSERT INTO Personnel VALUES('吉田', NULL,   NULL,   NULL);

需转换成下表：

 

 

方法及分析如下：/*先创建一个孩子的视图/* 孩子主表 

CREATE VIEW Children(child)
AS SELECT child_1 FROM Personnel
   UNION
   SELECT child_2 FROM Personnel
   UNION
   SELECT child_3 FROM Personnel; 
     

获取员工子女列表的SQL语句（没有孩子的员工也输出） */
SELECT EMP.employee, CHILDREN.child
  FROM Personnel EMP
       LEFT OUTER JOIN Children
    ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);

 

 

---

 

版权声明：本文章部分内容转载自《SQL进阶教程》作者：MICK ，出版社：人民邮电出版社，
文章仅供学习交流使用，转载请附上原文出处链接和本声明。