示例SQL

https://www.ituring.com.cn/book/1813

1 神奇的SQL

1-1 CASE表达式

可以使用其进行行列转换，已有数据重分组(分类)、与约束的结合使用。

CASE表达式的写法

-- 简单CASE表达式
CASE sex
  WHEN  ' 1 ' THEN  ' 男 '
  WHEN  ' 2 ' THEN  ' 女 '
ELSE  ' 其他 ' END
-- 搜索CASE表达式
CASE WHEN sex =  ' 1 ' THEN  ' 男 '
    WHEN sex =  '2' THEN  ' 女 '
ELSE  ' 其他 ' END

将已有编号方式转换为新的方式并统计

CREATE TABLE PopTbl
(pref_name VARCHAR(32) PRIMARY KEY,
 population INTEGER NOT NULL);
INSERT INTO PopTbl VALUES('德岛', 100);
INSERT INTO PopTbl VALUES('香川', 200);
INSERT INTO PopTbl VALUES('爱媛', 150);
INSERT INTO PopTbl VALUES('高知', 200);
INSERT INTO PopTbl VALUES('福冈', 300);
INSERT INTO PopTbl VALUES('佐贺', 100);
INSERT INTO PopTbl VALUES('长崎', 200);
INSERT INTO PopTbl VALUES('东京', 400);
INSERT INTO PopTbl VALUES('群马', 50);

/* 把县编号转换成地区编号（1） */
SELECT CASE pref_name
             WHEN '德岛' THEN '四国'
             WHEN '香川' THEN '四国'
             WHEN '爱媛' THEN '四国'
             WHEN '高知' THEN '四国'
             WHEN '福冈' THEN '九州'
             WHEN '佐贺' THEN '九州'
             WHEN '长崎' THEN '九州'
             ELSE '其他' END AS district,
       SUM(population)
  FROM PopTbl
 GROUP BY CASE pref_name
             WHEN '德岛' THEN '四国'
             WHEN '香川' THEN '四国'
             WHEN '爱媛' THEN '四国'
             WHEN '高知' THEN '四国'
             WHEN '福冈' THEN '九州'
             WHEN '佐贺' THEN '九州'
             WHEN '长崎' THEN '九州'
             ELSE '其他' END;

这里的关键在于将SELECT子句的CASE表达式复制到GRPUP BY子句里，如果对转换前的列‘pref_name’进行GROUPBY，就得不到准确的结果。

如果按照人口等级划分

/* 按人口数量等级划分都道府县 */
SELECT CASE WHEN population < 100 THEN '01'
            WHEN population >= 100 AND population < 200 THEN '02'
            WHEN population >= 200 AND population < 300 THEN '03'
            WHEN population >= 300 THEN '04'
            ELSE NULL END AS pop_class,
       COUNT(*) AS cnt
  FROM PopTbl
 GROUP BY CASE WHEN population < 100 THEN '01'
               WHEN population >= 100 AND population < 200 THEN '02'
               WHEN population >= 200 AND population < 300 THEN '03'
               WHEN population >= 300 THEN '04'
               ELSE NULL END;

这个技巧非常好用，不过，在SELECT子句和GROUP BY子句这两处写一样的CASE表达式，有点麻烦。可以写成下面的样子。

/* 把县编号转换成地区编号(2)：将CASE表达式归纳到一处 */
SELECT CASE pref_name
              WHEN '德岛' THEN '四国'
              WHEN '香川' THEN '四国'
              WHEN '爱媛' THEN '四国'
              WHEN '高知' THEN '四国'
              WHEN '福冈' THEN '九州'
              WHEN '佐贺' THEN '九州'
              WHEN '长崎' THEN '九州'
              ELSE '其他' END AS district,
       SUM(population)
  FROM PopTbl
 GROUP BY district;-- GROUP BY 子句里引用了 SELECT 子句中定义的别名

GROUP BY 子句里引用了 SELECT 子句中定义的别名
GROUP BY子句使用的正是SELECT子句里定义的列别名，严格来说这是违反SQL规则的。因为GROUP BY子句比SELECT语句先执行，所以在GROUP BY子句引用SELECT子句定义的别名是不被允许的。事实上，在Oracle DB2 SQL Server等这样的数据库采用这种写法就会出错。

用一条SQL语句进行不同条件的统计

/* 用一条SQL语句进行不同条件的统计 */
CREATE TABLE PopTbl2
(pref_name VARCHAR(32),
 sex CHAR(1) NOT NULL,
 population INTEGER NOT NULL,
    PRIMARY KEY(pref_name, sex));
INSERT INTO PopTbl2 VALUES('德岛', '1',    60 );
INSERT INTO PopTbl2 VALUES('德岛', '2',    40 );
INSERT INTO PopTbl2 VALUES('香川', '1',    100);
INSERT INTO PopTbl2 VALUES('香川', '2',    100);
INSERT INTO PopTbl2 VALUES('爱媛', '1',    100);
INSERT INTO PopTbl2 VALUES('爱媛', '2',    50 );
INSERT INTO PopTbl2 VALUES('高知', '1',    100);
INSERT INTO PopTbl2 VALUES('高知', '2',    100);
INSERT INTO PopTbl2 VALUES('福冈', '1',    100);
INSERT INTO PopTbl2 VALUES('福冈', '2',    200);
INSERT INTO PopTbl2 VALUES('佐贺', '1',    20 );
INSERT INTO PopTbl2 VALUES('佐贺', '2',    80 );
INSERT INTO PopTbl2 VALUES('长崎', '1',    125);
INSERT INTO PopTbl2 VALUES('长崎', '2',    125);
INSERT INTO PopTbl2 VALUES('东京', '1',    250);
INSERT INTO PopTbl2 VALUES('东京', '2',    150);

/* 用一条SQL语句进行不同条件的统计 */
SELECT pref_name,
       /* 男性人口 */
       SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m,
       /* 女性人口 */
       SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
  FROM PopTbl2
 GROUP BY pref_name;

在UPDATE语句里进行条件分支

/* 员工工资信息表 */
CREATE TABLE Salaries
(name VARCHAR(32) PRIMARY KEY,
 salary INTEGER NOT NULL);
INSERT INTO Salaries VALUES('相田', 300000);
INSERT INTO Salaries VALUES('神崎', 270000);
INSERT INTO Salaries VALUES('木村', 220000);
INSERT INTO Salaries VALUES('齐藤', 290000);

假设现在需要根据以下条件对该表的数据进行更新

对当前工资为30万日元以上的员工降薪10%。
对当前工资为25万日元且不满28万的员工，加薪20%

如果两条语句分开执行，假设一个员工，当前工资30万，按照条件1执行后，工资更新为27万，但继续按照条件2执行后，加薪到了32.4万。本应该降薪却被加薪2.4万

为了避免这种情况，使用CASE表达式

/* 用CASE表达式写正确的更新操作 */
UPDATE Salaries
   SET salary = CASE WHEN salary >= 300000
                     THEN salary * 0.9
                     WHEN salary >= 250000 AND salary < 280000
                     THEN salary * 1.2
                     ELSE salary END;

这样SQL语句不仅执行结果正确，而且只需要执行一次，所以速度更快。
SQL语句最后一行ELSE salary非常重要，必须写上。如果没有，不符合条件的员工工资将被更新为NULL。

/* 在UPDATE语句里进行条件分支 */
CREATE TABLE SomeTable
(p_key CHAR(1) PRIMARY KEY,
 col_1 INTEGER NOT NULL, 
 col_2 CHAR(2) NOT NULL);
INSERT INTO SomeTable VALUES('a', 1, '一');
INSERT INTO SomeTable VALUES('b', 2, '二');
INSERT INTO SomeTable VALUES('c', 3, '三');

如果在调换上表的主键值a和b时不用CASE表达式，则需要写3条SQL。使用CASE可以一次完成。

/* 用CASE表达式调换主键值 */
UPDATE SomeTable
   SET p_key = CASE WHEN p_key = 'a'
                    THEN 'b'
                    WHEN p_key = 'b'
                    THEN 'a'
                    ELSE p_key END
 WHERE p_key IN ('a', 'b');

表之间的数据匹配

与DECODE函数相比，CASE表达式的一个优势在于能判断表达式。也就是说，在CASE表达式里，可以使用BETWEEN LIKE < >等便利的组合，以及能嵌套子查询的IN和EXISTS谓词。因此CASE表达式具有非常强大的表达能力。

/* 表之间的数据匹配 */
CREATE TABLE CourseMaster
(course_id   INTEGER PRIMARY KEY,
 course_name VARCHAR(32) NOT NULL);
INSERT INTO CourseMaster VALUES(1, '会计入门');
INSERT INTO CourseMaster VALUES(2, '财务知识');
INSERT INTO CourseMaster VALUES(3, '簿记考试');
INSERT INTO CourseMaster VALUES(4, '税务师');
CREATE TABLE OpenCourses
(month       INTEGER ,
 course_id   INTEGER ,
    PRIMARY KEY(month, course_id));
INSERT INTO OpenCourses VALUES(200706, 1);
INSERT INTO OpenCourses VALUES(200706, 3);
INSERT INTO OpenCourses VALUES(200706, 4);
INSERT INTO OpenCourses VALUES(200707, 4);
INSERT INTO OpenCourses VALUES(200708, 2);
INSERT INTO OpenCourses VALUES(200708, 4);

/* 表的匹配：使用IN谓词 */
SELECT CM.course_name,
       CASE WHEN CM.course_id IN 
                    (SELECT course_id FROM OpenCourses 
                      WHERE month = 200706) THEN '○'
            ELSE '×' END AS "6月",
       CASE WHEN CM.course_id IN 
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 200707) THEN '○'
            ELSE '×' END AS "7月",
       CASE WHEN CM.course_id IN 
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 200708) THEN '○'
            ELSE '×' END  AS "8月"
  FROM CourseMaster CM;
/* 表的匹配：使用EXISTS谓词 */
SELECT CM.course_name,
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200706
                        AND CM.course_id = OC.course_id) THEN '○'
            ELSE '×' END AS "6月",
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200707
                        AND CM.course_id = OC.course_id) THEN '○'
            ELSE '×' END AS "7月",
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200708
                        AND CM.course_id = OC.course_id) THEN '○'
            ELSE '×' END  AS "8月"
  FROM CourseMaster CM;

+-------------+-----+-----+-----+
| course_name | 6月 | 7月 | 8月 |
+-------------+-----+-----+-----+
| 会计入门    | ○  | ×   | ×   |
| 财务知识    | ×   | ×   | ○  |
| 簿记考试    | ○  | ×   | ×   |
| 税务师      | ○  | ○  | ○  |
+-------------+-----+-----+-----+

这样的查询没有进行聚合，因此不需要排序，月份增加的时候仅修改SELECT子句即可。
无论使用IN还是EXISTS，得到的结果是一样的。但从性能的方面来说，EXISTS更好。通过EXIST进行的子查询能用到“month,course_id”这样的主键索引。

在CASE表达式中使用聚合函数

/* 在CASE表达式中使用聚合函数 */
CREATE TABLE StudentClub
(std_id  INTEGER,
 club_id INTEGER,
 club_name VARCHAR(32),
 main_club_flg CHAR(1),
 PRIMARY KEY (std_id, club_id));
INSERT INTO StudentClub VALUES(100, 1, '棒球',        'Y');
INSERT INTO StudentClub VALUES(100, 2, '管弦乐',      'N');
INSERT INTO StudentClub VALUES(200, 2, '管弦乐',      'N');
INSERT INTO StudentClub VALUES(200, 3, '羽毛球','Y');
INSERT INTO StudentClub VALUES(200, 4, '足球',    'N');
INSERT INTO StudentClub VALUES(300, 4, '足球',    'N');
INSERT INTO StudentClub VALUES(400, 5, '游泳',        'N');
INSERT INTO StudentClub VALUES(500, 6, '围棋',        'N');

对于加入了多个社团的学生，我们通过将其“主社团”列设置为Y或者N来表明哪一个社团是他们的主社团；对于值加入一个社团的学生，我们将其主社团表示设置为N。
按照条件查询表中数据

获取只加入了一个社团的学生的社团ID

获取加入了多个社团的学生的主社团ID

/* 在CASE表达式中使用聚合函数 */
SELECT std_id,
    CASE WHEN COUNT(*) = 1 /* 只加入了一个社团的学生 */
         THEN MAX(club_id)
         ELSE MAX(CASE WHEN main_club_flg = 'Y'
                       THEN club_id
                       ELSE NULL END)
     END AS main_club
FROM StudentClub
GROUP BY std_id;

小结

作为表达式，CASE表达式在执行时会被判定为一个固定值，因此他可以写在聚合函数内部；也因为它是表达式，所以还可以写在SELECT子句，GROUP BY子句，WHERE子句 ORDER BY子句里。简单来说，在能写列名和常量的地方，通常都可以写CASE表达式。

在GROYP BY子句里使用CASE表达式，可以灵活地选择作为聚合的单位的编号或等级。这一点在进行非定制化统计时能发挥巨大的威力。
在聚合函数中使用CASE表达式，可以轻松地将行结构的数据转换成列结构的数据。
聚合函数可以嵌套进CASE表达式里使用

demo

多列数据求最大值

CREATE TABLE Greatests
(key CHAR(1) PRIMARY KEY,
 x   INTEGER NOT NULL,
 y   INTEGER NOT NULL,
 z   INTEGER NOT NULL);

INSERT INTO Greatests VALUES('A', 1, 2, 3);
INSERT INTO Greatests VALUES('B', 5, 5, 2);
INSERT INTO Greatests VALUES('C', 4, 7, 1);
INSERT INTO Greatests VALUES('D', 3, 3, 8);

MySQL和Oracle提供了可以实现这个需求的GREATEST函数。
使用标准SQL。

-- 两列求组最大值
/* 求x和y二者中较大的值 */
SELECT key,
       CASE WHEN x < y THEN y
            ELSE x END AS greatest
  FROM Greatests;
/* 求x、y和z中的最大值 */
SELECT key,
       CASE WHEN CASE WHEN x < y THEN y ELSE x END < z
            THEN z
            ELSE CASE WHEN x < y THEN y ELSE x END
        END AS greatest
  FROM Greatests;

转换行列——在表头加入汇总数据

/* 转换行列——在表头里加入汇总和再揭(p.287) */
SELECT sex,
       SUM(population) AS total,
       SUM(CASE WHEN pref_name = '德岛' THEN population ELSE 0 END) AS col_1,
       SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS col_2,
       SUM(CASE WHEN pref_name = '爱媛' THEN population ELSE 0 END) AS col_3,
       SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS col_4,
       SUM(CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知')
                THEN population ELSE 0 END) AS zaijie
  FROM PopTbl2
 GROUP BY sex;

1-2 自连接的用法

可重排列、排列、组合

CREATE TABLE Products
(name VARCHAR(16) PRIMARY KEY,
 price INTEGER NOT NULL);

-- 可 重排列·排列·组合
INSERT INTO Products VALUES('苹果',    50);
INSERT INTO Products VALUES('橘子',    100);
INSERT INTO Products VALUES('香蕉',    80);

-- 排序
DELETE FROM Products;
INSERT INTO Products VALUES('苹果',    50);
INSERT INTO Products VALUES('橘子',    100);
INSERT INTO Products VALUES('葡萄',    50);
INSERT INTO Products VALUES('西瓜',    80);
INSERT INTO Products VALUES('柠檬',    30);
INSERT INTO Products VALUES('香蕉',    50);

-- 不聚合，查看集合的包含关系
DELETE FROM Products;
INSERT INTO Products VALUES('橘子',    100);
INSERT INTO Products VALUES('葡萄',    50);
INSERT INTO Products VALUES('西瓜',    80);
INSERT INTO Products VALUES('柠檬',    30);

/* 用于获取可重排列的SQL语句 */
SELECT P1.name AS name_1, P2.name AS name_2
  FROM Products P1, Products P2;


/* 用于获取排列的SQL语句 */
SELECT P1.name AS name_1, P2.name AS name_2
  FROM Products P1, Products P2
 WHERE P1.name <> P2.name;

不能用(苹果，苹果)这样的组合

/* 用于获取组合的SQL语句 */
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name > P2.name;

/* 用于获取组合的SQL语句：扩展成3列时 */
SELECT P1.name AS name_1, P2.name AS name_2, P3.name AS name_3
  FROM Products P1, Products P2, Products P3
WHERE P1.name > P2.name
  AND P2.name > P3.name;

“<”“>”等比较运算符不仅可以用于比较数值大小，也可以用于比较字符串或日期。

删除重复行

mariadb> SELECT * FROM Products ;
+------+-------+
| name | price |
+------+-------+
| 橘子 |   100 |
| 苹果 |    50 |
| 香蕉 |    80 |
| 橘子 |   100 |
| 橘子 |   100 |
+------+-------+
5 rows in set (0.03 sec)

需要使用数据库独自实现的行ID。
Oracle数据库是rowid

/* 用于删除重复行的SQL语句（1）：使用极值函数 */
DELETE FROM Products P1
 WHERE rowid < ( SELECT MAX(P2.rowid)
                   FROM Products P2
                  WHERE P1.name = P2. name
                    AND P1.price = P2.price ) ;


/* 用于删除重复行的SQL语句（2）：使用非等值连接 */
DELETE FROM Products P1
 WHERE EXISTS ( SELECT *
                  FROM Products P2
                 WHERE P1.name = P2.name
                   AND P1.price = P2.price
                   AND P1.rowid < P2.rowid );

查找局部不一致的列

--查找局部不一致的列
CREATE TABLE Addresses
(name VARCHAR(32),
 family_id INTEGER,
 address VARCHAR(32),
 PRIMARY KEY(name, family_id));

INSERT INTO Addresses VALUES('前田义明', '100', '东京都港区虎之门3-2-29');
INSERT INTO Addresses VALUES('前田由美', '100', '东京都港区虎之门3-2-92');
INSERT INTO Addresses VALUES('加藤茶',   '200', '东京都新宿区西新宿2-8-1');
INSERT INTO Addresses VALUES('加藤胜',   '200', '东京都新宿区西新宿2-8-1');
INSERT INTO Addresses VALUES('福尔摩斯',  '300', '贝克街221B');
INSERT INTO Addresses VALUES('华生',  '400', '贝克街221B');

/* 用于查找是同一家人但住址却不同的记录的SQL语句 */
SELECT DISTINCT A1.name, A1.address
  FROM Addresses A1, Addresses A2
 WHERE A1.family_id = A2.family_id
   AND A1.address <> A2.address ;

自连接和非等值连接结合起来确实非常好用。这条SQL不仅可以用于发现不规则的数据，而且修改一下可以用来查找商品。
从下面的表里早出街灯相等的商品组合

/* 用于查找价格相等但商品名称不同的记录的SQL语句 */
SELECT DISTINCT P1.name, P1.price
  FROM Products P1, Products P2
 WHERE P1.price = P2.price
   AND P1.name <> P2.name;

排序

在使用数据库制作各种票据和统计表的工作中，经常按照分数，人数和销售值进行排序。某些数据库已经实现了这样的功能(如Oracle DB2 数据库的 RANK函数)

按照价格从高到底的顺序，对下面这张表里的商品进行排序。一种是跳过之后的位次，另一种是不跳过之后的位次。

/* 排序：使用窗口函数 */
SELECT name, price,
       RANK() OVER (ORDER BY price DESC) AS rank_1,
       DENSE_RANK() OVER (ORDER BY price DESC) AS rank_2
  FROM Products;

+------+-------+--------+--------+
| name | price | rank_1 | rank_2 |
+------+-------+--------+--------+
| 橘子 |   100 |      1 |      1 |
| 西瓜 |    80 |      2 |      2 |
| 葡萄 |    50 |      3 |      3 |
| 香蕉 |    50 |      3 |      3 |
| 苹果 |    50 |      3 |      3 |
| 柠檬 |    30 |      6 |      4 |
+------+-------+--------+--------+

不依赖具体数据的窗口函数实现。下面用非等值自连接的写法

/* 排序从1开始。如果已出现相同位次，则跳过之后的位次 */
SELECT P1.name,
       P1.price,
      (SELECT COUNT(P2.price)
         FROM Products P2
        WHERE P2.price > P1.price) + 1 AS rank_1
 FROM Products P1
 ORDER BY rank_1;

上述代码看起来很普通，但是容易扩展，例如去掉标量子查询后面的+1，就可以从0开始给商品排序，如果修改成COUNT(DISTINCT P2.PRICE) 那么存在相同位次的记录时，就可以不跳过之后的位次，而是连续输出(相当于DENSE_RANK) 函数。

上述还可以按照自连接的写法

/* 排序：使用自连接 */
SELECT P1.name,
       MAX(P1.price) AS price,
       COUNT(P2.name) +1 AS rank_1
  FROM Products P1 LEFT OUTER JOIN Products P2
    ON P1.price < P2.price
 GROUP BY P1.name
 ORDER BY rank_1;

改用自连接，排名第一的“橘子”竟然从里面消失。

/* 排序：改为内连接 */
SELECT P1.name,
       MAX(P1.price) AS price,
       COUNT(P2.name) +1 AS rank_1
  FROM Products P1 INNER JOIN Products P2
    ON P1.price < P2.price
 GROUP BY P1.name
 ORDER BY rank_1;

外连接就是这样一个用于将第一名也存储在结果里的小技巧。

小结

与多想之间进行的普通连接相比，自连接的性能开销更大(特别是与非等值连接结合使用的时候)。因此用于自连接的列大多使用的主键。

demo

分地区排序

/* 练习题1-2-2：分地区排序 */
CREATE TABLE DistrictProducts
(district  VARCHAR(16) NOT NULL,
 name      VARCHAR(16) NOT NULL,
 price     INTEGER NOT NULL,
 PRIMARY KEY(district, name, price));

INSERT INTO DistrictProducts VALUES('东北', '橘子',    100);
INSERT INTO DistrictProducts VALUES('东北', '苹果',    50);
INSERT INTO DistrictProducts VALUES('东北', '葡萄',    50);
INSERT INTO DistrictProducts VALUES('东北', '柠檬',    30);
INSERT INTO DistrictProducts VALUES('关东', '柠檬',    100);
INSERT INTO DistrictProducts VALUES('关东', '菠萝',    100);
INSERT INTO DistrictProducts VALUES('关东', '苹果',    100);
INSERT INTO DistrictProducts VALUES('关东', '葡萄',    70);
INSERT INTO DistrictProducts VALUES('关西', '柠檬',    70);
INSERT INTO DistrictProducts VALUES('关西', '西瓜',    30);
INSERT INTO DistrictProducts VALUES('关西', '苹果',    20);

/* 练习题1-2-2 分地区排序 */
SELECT district, name, price,
          RANK() OVER(PARTITION BY district 
                      ORDER BY price DESC) AS rank_1
  FROM DistrictProducts;
/* 练习题1-2-2：自连接 */
SELECT P1.district, P1.name,
       MAX(P1.price) AS price, 
       COUNT(P2.name) +1 AS rank_1
  FROM DistrictProducts P1 LEFT OUTER JOIN DistrictProducts P2
    ON  P1.district = P2.district
   AND P1.price < P2.price
 GROUP BY P1.district, P1.name;

更新谓位次

/* 练习题1-2-3：更新位次 */
CREATE TABLE DistrictProducts2
(district  VARCHAR(16) NOT NULL,
 name      VARCHAR(16) NOT NULL,
 price     INTEGER NOT NULL,
 ranking   INTEGER,
 PRIMARY KEY(district, name));

INSERT INTO DistrictProducts2 VALUES('东北', '橘子',    100, NULL);
INSERT INTO DistrictProducts2 VALUES('东北', '苹果',    50 , NULL);
INSERT INTO DistrictProducts2 VALUES('东北', '葡萄',    50 , NULL);
INSERT INTO DistrictProducts2 VALUES('东北', '柠檬',    30 , NULL);
INSERT INTO DistrictProducts2 VALUES('关东', '柠檬',    100, NULL);
INSERT INTO DistrictProducts2 VALUES('关东', '菠萝',    100, NULL);
INSERT INTO DistrictProducts2 VALUES('关东', '苹果',    100, NULL);
INSERT INTO DistrictProducts2 VALUES('关东', '葡萄',    70 , NULL);
INSERT INTO DistrictProducts2 VALUES('关西', '柠檬',    70 , NULL);
INSERT INTO DistrictProducts2 VALUES('关西', '西瓜',    30 , NULL);
INSERT INTO DistrictProducts2 VALUES('关西', '苹果',    20 , NULL);

mariadb> select * from DistrictProducts2;
+----------+------+-------+---------+
| district | name | price | ranking |
+----------+------+-------+---------+
| 东北     | 柠檬 |    30 | NULL    |
| 东北     | 橘子 |   100 | NULL    |
| 东北     | 苹果 |    50 | NULL    |
| 东北     | 葡萄 |    50 | NULL    |
| 关东     | 柠檬 |   100 | NULL    |
| 关东     | 苹果 |   100 | NULL    |
| 关东     | 菠萝 |   100 | NULL    |
| 关东     | 葡萄 |    70 | NULL    |
| 关西     | 柠檬 |    70 | NULL    |
| 关西     | 苹果 |    20 | NULL    |
| 关西     | 西瓜 |    30 | NULL    |
+----------+------+-------+---------+

位次初始值都是NULL，写入次序

/* 练习题1-2-3：更新位次 */
UPDATE DistrictProducts2 P1
   SET ranking = (SELECT COUNT(P2.price) + 1
                    FROM DistrictProducts2 P2
                   WHERE P1.district = P2.district
                     AND P2.price > P1.price);

UPDATE DistrictProducts2
   SET ranking =
         (SELECT P1.ranking
            FROM (SELECT district , name ,
                         RANK() OVER(PARTITION BY district
                                     ORDER BY price DESC) AS ranking
                    FROM DistrictProducts2) P1
                   WHERE P1.district = DistrictProducts2.district
                     AND P1.name = DistrictProducts2.name);

1-3 三值逻辑和NULL

比较谓词和NULL：排中律不成立

/* 1.比较谓词和NULL（1）：排中律不成立 */
CREATE TABLE Students
(name VARCHAR(16) PRIMARY KEY,
 age  INTEGER );

INSERT INTO Students VALUES('布朗', 22);
INSERT INTO Students VALUES('拉里',   19);
INSERT INTO Students VALUES('约翰',   NULL);
INSERT INTO Students VALUES('伯杰', 21);

+------+------+
| name | age  |
+------+------+
| 伯杰 |   21 |
| 布朗 |   22 |
| 拉里 |   19 |
| 约翰 | NULL |

mariadb> /* 查询年龄是20岁或者不是20岁的学生 */
SELECT *
  FROM Students
 WHERE age = 20
    OR age <> 20;
+------+-----+
| name | age |
+------+-----+
| 伯杰 |  21 |
| 布朗 |  22 |
| 拉里 |  19 |
+------+-----+
-- 查询中少了约翰

-- 未知的NULL
mariadb> SELECT *
  FROM Students
 WHERE age = null
    OR age <> null;
Empty set

/* 添加第3个条件：年龄是20岁，或者不是20岁，或者年龄未知 */
SELECT *
  FROM Students
 WHERE age = 20
    OR age <> 20
    OR age IS NULL;

比较谓词和NULL：CASE表达式和NULL

在CASE表达式里将NULL作为条件时经常会出现错误

-- col_1 为 1 时返回○、为 NULL 时返回 × 的 CASE 表达式？
CASE col_1
  WHEN 1 THEN  ' ○ '
  WHEN NULL THEN  ' × '
END

这个表达式不一定返回X，这是因为，第二个WHEN子句是col_1 = null 的缩写形式，正确的写法如下

NOT IN和NOT EXISTS 不是等价的

在对SQL语句进行优化时，经常使用到的一个技巧是将IN 改写成EXISTS。这是等价的，问题在于，将NOT IN改写成NOT EXISTS 时，结果未必一样。

CREATE TABLE Class_A
(name VARCHAR(16) PRIMARY KEY,
 age  INTEGER,
 city VARCHAR(16) NOT NULL );

CREATE TABLE Class_B
(name VARCHAR(16) PRIMARY KEY,
 age  INTEGER,
 city VARCHAR(16) NOT NULL );

INSERT INTO Class_A VALUES('布朗', 22, '东京');
INSERT INTO Class_A VALUES('拉里',   19, '埼玉');
INSERT INTO Class_A VALUES('伯杰',   21, '千叶');

INSERT INTO Class_B VALUES('齐藤',  22,   '东京');
INSERT INTO Class_B VALUES('田尻',  23,   '东京');
INSERT INTO Class_B VALUES('山田',  NULL, '东京');
INSERT INTO Class_B VALUES('和泉',  18,   '千叶');
INSERT INTO Class_B VALUES('武田',  20,   '千叶');
INSERT INTO Class_B VALUES('石川',  19,   '神奈川');

mariadb> select * from Class_A ;
+------+-----+------+
| name | age | city |
+------+-----+------+
| 伯杰 |  21 | 千叶 |
| 布朗 |  22 | 东京 |
| 拉里 |  19 | 埼玉 |
+------+-----+------+
3 rows in set (0.03 sec)

mariadb> select * from Class_B; 
+------+------+--------+
| name | age  | city   |
+------+------+--------+
| 和泉 |   18 | 千叶   |
| 山田 | NULL | 东京   |
| 武田 |   20 | 千叶   |
| 田尻 |   23 | 东京   |
| 石川 |   19 | 神奈川 |
| 齐藤 |   22 | 东京   |
+------+------+--------+

查询“与B班住在东京的学生年龄不同的A班学生”。希望查询到的是拉里和伯杰

/* 查询与B班住在东京的学生年龄不同的A班学生的SQL语句？ */
SELECT *
  FROM Class_A
 WHERE age NOT IN ( SELECT age
                      FROM Class_B
                     WHERE city = '东京' );

如果NOT IN子查询中用到的表里被选择的列中存在NULL，则SQL语句的查询结果永远是空。

为了查询正确的结果，需要使用EXISTS谓词

/* 正确的SQL语句：拉里和伯杰将被查询到 */
SELECT *
  FROM Class_A A
 WHERE NOT EXISTS ( SELECT age
                      FROM Class_B B
                     WHERE A.age = B.age
                       AND B.city = '东京' );

限定谓词和NULL

SQL里有ALL和ANY两个限定谓词。因为ANY和IN是等价的，所以我们吃经常使用ANY。

DELETE FROM Class_A;
INSERT INTO Class_A VALUES('布朗', 22, '东京');
INSERT INTO Class_A VALUES('拉里',   19, '埼玉');
INSERT INTO Class_A VALUES('伯杰',   21, '千叶');

DELETE FROM Class_B;
INSERT INTO Class_B VALUES('齐藤', 22, '东京');
INSERT INTO Class_B VALUES('田尻', 23, '东京');
INSERT INTO Class_B VALUES('山田', 20, '东京');
INSERT INTO Class_B VALUES('和泉', 18, '千叶');
INSERT INTO Class_B VALUES('武田', 20, '千叶');
INSERT INTO Class_B VALUES('石川', 19, '神奈川');

如果存在NULL，上述查询就有问题

限定谓词和极值函数是不等价的

使用极值函数替代ALL谓词。

/* 查询比住在东京的学生的平均年龄还要小的A班学生的SQL语句？ */
SELECT *
  FROM Class_A
 WHERE age < ( SELECT AVG(age)
                 FROM Class_B
                WHERE city = '东京' );

1-4 HAVING子句的力量

HAVIGN子句是SQL里非常重要的功能。它还是理解SQL面向集合这一本质的关键(以集合为单位进行操作)。

寻找缺失的编号

/* 寻找缺失的编号 */
CREATE TABLE SeqTbl
(seq  INTEGER PRIMARY KEY,
 name VARCHAR(16) NOT NULL);

INSERT INTO SeqTbl VALUES(1,    '迪克');
INSERT INTO SeqTbl VALUES(2,    '安');
INSERT INTO SeqTbl VALUES(3,    '莱露');
INSERT INTO SeqTbl VALUES(5,    '卡');
INSERT INTO SeqTbl VALUES(6,    '玛丽');
INSERT INTO SeqTbl VALUES(8,    '本');

/* 如果有查询结果，说明存在缺失的编号 */
SELECT '存在缺失的编号' AS gap
  FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

上述SQL语句中没有GROUP BY子句，此时整张表被聚合为一行。这种情况下HAVING子句也是可以使用的。在以前的SQL标准里，HAVING子句必须和GROUP BY子句一起使用。

/* 查询缺失编号的最小值 */
SELECT MIN(seq + 1) AS gap
  FROM SeqTbl
 WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);

如果存在NULL或者最小值为1 ，这条语句不正确

用HAVING子句进行子查询：求众数

/* 用HAVING子句进行子查询：求众数(求中位数时也用本代码) */
CREATE TABLE Graduates
(name   VARCHAR(16) PRIMARY KEY,
 income INTEGER NOT NULL);

INSERT INTO Graduates VALUES('桑普森', 400000);
INSERT INTO Graduates VALUES('迈克',     30000);
INSERT INTO Graduates VALUES('怀特',   20000);
INSERT INTO Graduates VALUES('阿诺德', 20000);
INSERT INTO Graduates VALUES('史密斯',     20000);
INSERT INTO Graduates VALUES('劳伦斯',   15000);
INSERT INTO Graduates VALUES('哈德逊',   15000);
INSERT INTO Graduates VALUES('肯特',     10000);
INSERT INTO Graduates VALUES('贝克',   10000);
INSERT INTO Graduates VALUES('斯科特',   10000);

/* 求众数的SQL语句（1）：使用谓词 */
  SELECT income, COUNT(*) AS cnt
    FROM Graduates
   GROUP BY income
  HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
                             FROM Graduates
                         GROUP BY income);
--  ALL 谓词用于 NULL 或空集时会出现问题                         
/* 求众数的SQL语句(2)：使用极值函数 */
SELECT income, COUNT(*) AS cnt
  FROM Graduates
 GROUP BY income
HAVING COUNT(*) >=  ( SELECT MAX(cnt)
                        FROM ( SELECT COUNT(*) AS cnt
                                 FROM Graduates
                             GROUP BY income) TMP) ;

用HAVING子句进行自连接：求中位数

/* 求中位数的SQL语句：在HAVING子句中使用非等值自连接 */
SELECT AVG(DISTINCT income)
  FROM (SELECT T1.income
          FROM Graduates T1, Graduates T2
      GROUP BY T1.income
               /* S1的条件 */
        HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) 
                   >= COUNT(*) / 2
               /* S2的条件 */
           AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) 
                   >= COUNT(*) / 2 ) TMP;

原理解析

查询不包含NULL的集合

/* 查询不包含NULL的集合 */
CREATE TABLE Students
(student_id   INTEGER PRIMARY KEY,
 dpt          VARCHAR(16) NOT NULL,
 sbmt_date    DATE);

INSERT INTO Students VALUES(100,  '理学院',   '2005-10-10');
INSERT INTO Students VALUES(101,  '理学院',   '2005-09-22');
INSERT INTO Students VALUES(102,  '文学院',   NULL);
INSERT INTO Students VALUES(103,  '文学院',   '2005-09-10');
INSERT INTO Students VALUES(200,  '文学院',   '2005-09-22');
INSERT INTO Students VALUES(201,  '工学院',   NULL);
INSERT INTO Students VALUES(202,  '经济学院', '2005-09-25');

查询全部提交的学院

/* 查询“提交日期”列内不包含NULL的学院(1)：使用COUNT函数 */
SELECT dpt
  FROM Students
 GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);
/* 查询“提交日期”列内不包含NULL的学院(2)：使用CASE表达式 */
SELECT dpt
  FROM Students
 GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL
                           THEN 1
                           ELSE 0 END);

用关系除法运算进行购物篮分析

查询囊括了表Items中所有的商品的店铺。
像表ShopTtems这种一个实体的信息分散在多行的时候，仅仅在WHERE子句里通过OR 或者IN 条件是无法得到正确结果的，这是因为，在WHERE子句里指定的条件只对表里的某一行数据有效。

/* 查询啤酒、纸尿裤和自行车同时在库的店铺：错误的SQL语句 */
SELECT DISTINCT shop
  FROM ShopItems
 WHERE item IN (SELECT item FROM Items);

/* 查询啤酒、纸尿裤和自行车同时在库的店铺：正确的SQL语句 */
SELECT SI.shop
  FROM ShopItems SI, Items I
 WHERE SI.item = I.item
 GROUP BY SI.shop
HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items);

谓词IN的条件其实只是指定了“店内有啤酒或者纸尿裤或者自行车的店铺”，只有店铺有这三种任何一种，就会出现在查询里，如果真多行数据设定查询，就需要用HAVING子句来解决这个问题。

/* 精确关系除法运算：使用外连接和COUNT函数 */
  SELECT *
    FROM ShopItems AS SI LEFT OUTER JOIN Items AS I
      ON SI.item=I.item
GROUP BY SI.shop
  HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items)   /* 条件1 */
     AND COUNT(I.item)  = (SELECT COUNT(item) FROM Items);  /* 条件2 */

1-5 外连接的用法

用外连接进行行列转换(行->列)：制作交叉表

/* 用外连接进行行列转换（1）（行→列）：制作交叉表 */
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）：使用标量子查询 */
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; -- 这里的 C0 是表侧栏

这种做法不仅利于应对需求的变更，对于需要动态生成SQL的系统来说也是有好处的。缺点是性能不太好。目前在SELECT子句中使用标量子查询(或者关联子查询)，性能开销还是相当大的。

/* 水平展开（3）：嵌套使用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;

用外连接进行行列转换(2)(行-列)：汇总重复项于一行

/* 用外连接进行行列转换（2）（列→行）：汇总重复项于一列 */
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);

/* 列数据转换成行数据：使用UNION ALL */
SELECT employee, child_1 AS child FROM Personnel
UNION ALL
SELECT employee, child_2 AS child FROM Personnel
UNION ALL
SELECT employee, child_3 AS child FROM Personnel;

因为NUIONall不会排查重复行，所以即使吉田没有孩子，结果也会出现3行相关数据。

生成一个存储子女列表的视图。

/* 孩子主表 */
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);

在交叉表里制作嵌套是表侧栏

/* 在交叉表里制作嵌套式表侧栏 */
CREATE TABLE TblSex
(sex_cd   char(1), 
 sex varchar(5), 
 PRIMARY KEY(sex_cd));

CREATE TABLE TblAge 
(age_class char(1), 
 age_range varchar(30), 
 PRIMARY KEY(age_class));

CREATE TABLE TblPop 
(pref_name  varchar(30), 
 age_class  char(1), 
 sex_cd     char(1), 
 population integer, 
 PRIMARY KEY(pref_name, age_class,sex_cd));

INSERT INTO TblSex (sex_cd, sex ) VALUES('m',    '男');
INSERT INTO TblSex (sex_cd, sex ) VALUES('f',    '女');

INSERT INTO TblAge (age_class, age_range ) VALUES('1',    '21岁～30岁');
INSERT INTO TblAge (age_class, age_range ) VALUES('2',    '31岁～40岁');
INSERT INTO TblAge (age_class, age_range ) VALUES('3',    '41岁～50岁');

INSERT INTO TblPop VALUES('秋田', '1', 'm', 400 );
INSERT INTO TblPop VALUES('秋田', '3', 'm', 1000 );
INSERT INTO TblPop VALUES('秋田', '1', 'f', 800 );
INSERT INTO TblPop VALUES('秋田', '3', 'f', 1000 );
INSERT INTO TblPop VALUES('青森', '1', 'm', 700 );
INSERT INTO TblPop VALUES('青森', '1', 'f', 500 );
INSERT INTO TblPop VALUES('青森', '3', 'f', 800 );
INSERT INTO TblPop VALUES('东京', '1', 'm', 900 );
INSERT INTO TblPop VALUES('东京', '1', 'f', 1500 );
INSERT INTO TblPop VALUES('东京', '3', 'f', 1200 );
INSERT INTO TblPop VALUES('千叶', '1', 'm', 900 );
INSERT INTO TblPop VALUES('千叶', '1', 'f', 1000 );
INSERT INTO TblPop VALUES('千叶', '3', 'f', 900 );

统计结果如下

/* 使用外连接生成嵌套式表侧栏：错误的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_kanto
  FROM (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
        RIGHT OUTER JOIN TblAge MASTER1 /* 外连接1：和年龄层级主表进行外连接 */
           ON MASTER1.age_class = DATA.age_class
              RIGHT OUTER JOIN TblSex MASTER2 /* 外连接2：和性别主表进行外连接 */
                 ON MASTER2.sex_cd = DATA.sex_cd;

+-----------+--------+------------+-----------+
| age_class | sex_cd | pop_tohoku | pop_kanto |
+-----------+--------+------------+-----------+
| 3         | f      | 1800       | 2100      |
| 1         | f      | 1300       | 2500      |
| 3         | m      | 1000       | NULL      |
| 1         | m      | 1100       | 1800      |

mariadb> /* 停在第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_kanto
  FROM (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
        RIGHT OUTER JOIN TblAge MASTER1
           ON MASTER1.age_class = DATA.age_class;
+-----------+--------+------------+-----------+
| age_class | sex_cd | pop_tohoku | pop_kanto |
+-----------+--------+------------+-----------+
| 1         | m      | 1100       | 1800      |
| 1         | f      | 1300       | 2500      |
| 2         | NULL   | NULL       | NULL      |
| 3         | m      | 1000       | NULL      |
| 3         | f      | 1800       | 2100      |
+-----------+--------+------------+-----------+

/* 使用外连接生成嵌套式表侧栏：正确的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_kanto
FROM
  (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
     RIGHT OUTER JOIN
       (SELECT age_class, sex_cd
          FROM TblAge 
                CROSS JOIN
               TblSex ) MASTER
     ON  MASTER.age_class = DATA.age_class
    AND  MASTER.sex_cd    = DATA.sex_cd;

作为乘法运算的连接

/* 作为乘法运算的连接 */
drop TABLE if EXISTS items ;
CREATE TABLE Items
 (item_no INTEGER PRIMARY KEY,
  item    VARCHAR(32) NOT NULL);

INSERT INTO Items VALUES(10, 'FD');
INSERT INTO Items VALUES(20, 'CD-R');
INSERT INTO Items VALUES(30, 'MO');
INSERT INTO Items VALUES(40, 'DVD');

CREATE TABLE SalesHistory
 (sale_date DATE NOT NULL,
  item_no   INTEGER NOT NULL,
  quantity  INTEGER NOT NULL,
  PRIMARY KEY(sale_date, item_no));

INSERT INTO SalesHistory VALUES('2007-10-01',  10,  4);
INSERT INTO SalesHistory VALUES('2007-10-01',  20, 10);
INSERT INTO SalesHistory VALUES('2007-10-01',  30,  3);
INSERT INTO SalesHistory VALUES('2007-10-03',  10, 32);
INSERT INTO SalesHistory VALUES('2007-10-03',  30, 12);
INSERT INTO SalesHistory VALUES('2007-10-04',  20, 22);
INSERT INTO SalesHistory VALUES('2007-10-04',  30,  7);

使用两张表生成一张统计表，以商品单位汇总出各自的销量。
因为没有销售记录的40号商品也需要输出，所以需要使用外连接。

/* 解答（1）：通过在连接前聚合来创建一对一的关系 */
SELECT I.item_no, SH.total_qty
  FROM Items I LEFT OUTER JOIN
       (SELECT item_no, SUM(quantity) AS total_qty
          FROM SalesHistory
         GROUP BY item_no) SH
    ON I.item_no = SH.item_no;
+---------+-----------+
| item_no | total_qty |
+---------+-----------+
|      10 | 36        |
|      20 | 32        |
|      30 | 22        |
|      40 | NULL      |
+---------+-----------+
4 rows in set (0.03 sec)

上述以商品为编号作为主键的临时视图SH。从性能的角度考虑，这条SQL有些问题，比如临时表视图SH需要临时存储在内存中，还有就是虽然通过聚合将item_no变成了主键，但是SH上却不存在主键索引，我们无法利用索引优化查询。

商品主表Items和视图SH确实是一对一的关系，从“item_no”列表看，表Items和SalesHistory是一对多的关系。而且，当连接操作的双方是一对多关系时，结果的行数并不会在增加，因此可以如下改写。

/* 解答(2)：先进行一对多的连接再聚合 */
SELECT I.item_no, SUM(SH.quantity) AS total_qty
  FROM Items I LEFT OUTER JOIN SalesHistory SH
    ON I.item_no = SH.item_no /* 一对多的连接 */
 GROUP BY I.item_no;

如果Items里的“Items_no”列内存在重复行，就属于多对多连接了。因而不能使用这种做法。
一对一或一对多的两个集合，在进行连接操作后行数不会增加。

全外连接

/* 作为乘法运算的连接 */
drop TABLE if EXISTS Class_A ;
drop TABLE if EXISTS Class_B ;
/* 全外连接 */
CREATE TABLE Class_A
(id char(1), 
 name varchar(30), 
 PRIMARY KEY(id));

CREATE TABLE Class_B
(id   char(1), 
 name varchar(30), 
 PRIMARY KEY(id));

INSERT INTO Class_A (id, name) VALUES('1', '田中');
INSERT INTO Class_A (id, name) VALUES('2', '铃木');
INSERT INTO Class_A (id, name) VALUES('3', '伊集院');

INSERT INTO Class_B (id, name) VALUES('1', '田中');
INSERT INTO Class_B (id, name) VALUES('2', '铃木');
INSERT INTO Class_B (id, name) VALUES('4', '西园寺');

1-6 用关联子查询比较行与行

增长、减少、维持现状

使用SQL输出与上一年相比，营业额是增加还是减少，抑或没有变化。从表中可以看出，1993年和1995年没有变化。

/* 求与上一年营业额一样的年份（1）：使用关联子查询 */
SELECT year,sale
  FROM Sales S1
 WHERE sale = (SELECT sale
                 FROM Sales S2
                WHERE S2.year = S1.year - 1)
 ORDER BY year ;

用列表展示与上一年的比较结果

/* 求出是增长了还是减少了，抑或是维持现状（1）：使用关联子查询 */
SELECT S1.year, S1.sale,
       CASE WHEN sale =
             (SELECT sale
                FROM Sales S2
               WHERE S2.year = S1.year - 1) THEN '→' /* 持平 */
            WHEN sale >
             (SELECT sale
                FROM Sales S2
               WHERE S2.year = S1.year - 1) THEN '↑' /* 增长 */
            WHEN sale <
             (SELECT sale
                FROM Sales S2
               WHERE S2.year = S1.year - 1) THEN '↓' /* 减少 */
       ELSE '—' END AS var
  FROM Sales S1
 ORDER BY year;

/* 求出是增长了还是减少了，抑或是维持现状（1）：使用关联子查询 */
SELECT S1.year, S1.sale,
       CASE WHEN sale =
             (SELECT sale
                FROM Sales S2
               WHERE S2.year = S1.year - 1) THEN '→' /* 持平 */
            WHEN sale >
             (SELECT sale
                FROM Sales S2
               WHERE S2.year = S1.year - 1) THEN '↑' /* 增长 */
            WHEN sale <
             (SELECT sale
                FROM Sales S2
               WHERE S2.year = S1.year - 1) THEN '↓' /* 减少 */
       ELSE '—' END AS var
  FROM Sales S1
 ORDER BY year;

  /* 求出是增长了还是减少了，抑或是维持现状（2）：使用自连接查询 */
SELECT S1.year, S1.sale,
       CASE WHEN S1.sale = S2.sale THEN '→' 
            WHEN S1.sale > S2.sale THEN '↑' 
            WHEN S1.sale < S2.sale THEN '↓' 
       ELSE '—' END AS var
  FROM Sales S1, Sales S2
 WHERE S2.year = S1.year-1
 ORDER BY year;

时间轴有间断时：和过去最近的时间进行比较

-- 时间轴有间断时：和过去最临近的时间进行比较
CREATE TABLE Sales2
(year INTEGER NOT NULL , 
 sale INTEGER NOT NULL , 
 PRIMARY KEY (year));

INSERT INTO Sales2 VALUES (1990, 50);
INSERT INTO Sales2 VALUES (1992, 50);
INSERT INTO Sales2 VALUES (1993, 52);
INSERT INTO Sales2 VALUES (1994, 55);
INSERT INTO Sales2 VALUES (1997, 55);

这样一来，“年份-1”这个条件了。需要扩展成更普遍的情况，用某一年的数据和它过去最临近的年份比较。具体就是，让1992年和1990年比较。
对某一年来说，“过去最临近的年份”满足下面两个条件。

与该年份相比是过去的年份
在满足条件1的年份中，年份最早的一个。 ```sql / 查询与过去最临近的年份营业额相同的年份 / SELECT year, sale FROM Sales2 S1 WHERE sale = (SELECT sale FROM Sales2 S2 WHERE S2.year =
```
(SELECT MAX(year)            /* 条件2：在满足条件1的年份中，年份最早的一个 */
   FROM Sales2 S3
  WHERE S1.year > S3.year))  /* 条件1：与该年份相比是过去的年份 */
```
ORDER BY year;


如果使用自连接，可以减少一层子查询的嵌套

通过这个方法，可以每一年与过去最临近的年份之间的营业额之差。
```sql
/* 求每一年与过去最临近的年份之间的营业额之差（1）：结果里不包含最早的年份 */
SELECT S2.year AS pre_year,
       S1.year AS now_year,
       S2.sale AS pre_sale,
       S1.sale AS now_sale,
       S1.sale - S2.sale  AS diff
 FROM Sales2 S1, Sales2 S2
 WHERE S2.year = (SELECT MAX(year)
                    FROM Sales2 S3
                   WHERE S1.year > S3.year)
 ORDER BY now_year;

上述语句无法获取1990年的数据，如果出现1990年的数据，使用“自外连接”来实现。

/* 求每一年与过去最临近的年份之间的营业额之差（2）：使用自外连接。结果里包含最早的年份 */
SELECT S2.year AS pre_year,
       S1.year AS now_year,
       S2.sale AS pre_sale,
       S1.sale AS now_sale,
       S1.sale - S2.sale AS diff
 FROM Sales2 S1 LEFT OUTER JOIN Sales2 S2
   ON S2.year = (SELECT MAX(year)
                   FROM Sales2 S3
                  WHERE S1.year > S3.year)
 ORDER BY now_year;

移动累积值和移动平均值

把截止到某个时间点且按时间记录的数值累加而得出来的数值称为累计值。

-- 移动累计值和移动平均值
CREATE TABLE Accounts
(prc_date DATE NOT NULL , 
 prc_amt  INTEGER NOT NULL , 
 PRIMARY KEY (prc_date)) ;

INSERT INTO Accounts VALUES ('2006-10-26',  12000 );
INSERT INTO Accounts VALUES ('2006-10-28',   2500 );
INSERT INTO Accounts VALUES ('2006-10-31', -15000 );
INSERT INTO Accounts VALUES ('2006-11-03',  34000 );
INSERT INTO Accounts VALUES ('2006-11-04',  -5000 );
INSERT INTO Accounts VALUES ('2006-11-06',   7200 );
INSERT INTO Accounts VALUES ('2006-11-11',  11000 );

使用窗口函数

/* 求累计值：使用窗口函数 */
SELECT prc_date, prc_amt,
       SUM(prc_amt) OVER (ORDER BY prc_date) AS onhand_amt
  FROM Accounts;


/* 求累计值：使用冯·诺依曼型递归集合 */
SELECT prc_date, A1.prc_amt,
      (SELECT SUM(prc_amt)
         FROM Accounts A2
        WHERE A1.prc_date >= A2.prc_date ) AS onhand_amt
  FROM Accounts A1
 ORDER BY prc_date;

如何以3次处理为单位求累计值，即移动累计值。所谓移动，指的是将累计的数据行数固定，一行一行地偏移。如下

思路是，往刚才的求累计值的SQL语句里加上这样一个条件：A2的处理日期与A1的处理日期之间的记录在3行以内。如果使用窗口函数可以像下面通过指定ROWS关键字来指定数据行数。

/* 求移动累计值（1）：使用窗口函数 */
SELECT prc_date, prc_amt,
       SUM(prc_amt) OVER (ORDER BY prc_date
                           ROWS 2 PRECEDING) AS onhand_amt
  FROM Accounts;

SQL进阶教程

示例SQL

1 神奇的SQL

1-1 CASE表达式

CASE表达式的写法

将已有编号方式转换为新的方式并统计

用一条SQL语句进行不同条件的统计

在UPDATE语句里进行条件分支

表之间的数据匹配

在CASE表达式中使用聚合函数

小结

demo

多列数据求最大值

转换行列——在表头加入汇总数据

1-2 自连接的用法

可重排列、排列、组合

不能用(苹果，苹果)这样的组合

删除重复行

查找局部不一致的列

排序

小结

demo

分地区排序

更新谓位次

1-3 三值逻辑和NULL

比较谓词和NULL：排中律不成立

比较谓词和NULL：CASE表达式和NULL

NOT IN和NOT EXISTS 不是等价的

限定谓词和NULL

限定谓词和极值函数是不等价的

1-4 HAVING子句的力量

寻找缺失的编号

用HAVING子句进行子查询：求众数

用HAVING子句进行自连接：求中位数

查询不包含NULL的集合

用关系除法运算进行购物篮分析

1-5 外连接的用法

用外连接进行行列转换(行->列)：制作交叉表

用外连接进行行列转换(2)(行-列)：汇总重复项于一行

在交叉表里制作嵌套是表侧栏

作为乘法运算的连接

全外连接

1-6 用关联子查询比较行与行

增长、减少、维持现状

用列表展示与上一年的比较结果

时间轴有间断时：和过去最近的时间进行比较

移动累积值和移动平均值