SQL使用技巧

前言

本博客用于记录本人在学习SQL知识过程中遇见的各类值得记录的学习技巧。

1. CASE表达式

1.1 聚合结果行列转换

问题：
假设有如下表格sample1_1：

item	price
电脑	2000
手机	1000
电视	3000
手机	1000
电视	3000
电脑	2000
手机	1000
电视	3000
电脑	2000
电视	3000
手机	1000
电视	3000

现要求得到如下结果：

num_computer	num_phone	num_TV
3	4	5

解决方法：
乍一看这个结果是对表格中的项目进行了分组并统计了数量，初始觉得应该使用 GROUP BY 子句，但是进一步观察所要求的结果与 GROUP BY 子句汇总的结果并不一样，相当于对GROUP BY 汇总结果进行了转置，结果有几列就应该在SELECT子句中选取几列。这一问题是用CASE表达式进行解决的：

SELECT SUM(CASE WHEN item = "电脑" THEN 1 ELSE 0 END) AS num_computer, 
    SUM(CASE WHEN item = "手机" THEN 1 ELSE 0 END) AS num_phone,
    SUM(CASE WHEN item = "电视" THEN 1 ELSE 0 END) AS num_TV
FROM sample1_1

1.2 已有数据重分组

问题：
假设有如下表格sample1_2：

pref_name（县名）	population（人口）
德岛	100
香川	200
爱媛	150
高知	200
福冈	300
佐贺	100
长崎	200
东京	400
群马	50

现要求以东北、关东、九州等地区为单位来分组，并统计人口数量，以得到如下结果：

地区名	人口
四国	650
九州	600
其他	450

其中，“四国”对应的是表 sample1_2 中的“德岛、香川、爱媛、高知”，“九州”对应的是表 sample1_2 中的“福冈、佐贺、长崎”。

解决方法：
这个问题的常见思路是，将 sample1_2 与另一包含地区信息的表进行联结，然后再根据地区列进行分组统计即可。而如果使用 CASE 表达式，则用如下所示的一条 SQL 语句就可以完成：

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 sample1_2 
GROUP BY CASE pref_name
			WHEN  '德岛' THEN  '四国'
			WHEN  '香川' THEN  '四国'
			WHEN  '爱媛' THEN  '四国'
			WHEN  '高知' THEN  '四国'
			WHEN  '福冈' THEN  '九州'
			WHEN  '佐贺' THEN  '九州'
			WHEN  '长崎' THEN  '九州'
		  ELSE  '其他' END;

这里的关键在于将 SELECT 子句里的 CASE 表达式复制到 GROUP BY 子句里。不过，必须在 SELECT 子句和 GROUP BY 子句这两处写一样的 CASE 表达式，这有点儿麻烦。后期需要修改的时候，很容易发生只改了这一处而忘掉改另一处的失误。

MySQL 支持如下改写：

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 sample1_2 
GROUP BY district;

上述 GROUP BY 子句使用的是 SELECT 子句里定义的列别称 district。严格来说，这种写法违反了标准 SQL 的规则，因为 GROUP BY 子句比 SELECT 语句先执行，所以在 GROUP BY 子句中引用在 SELECT 子句里定义的别称是不被允许的。不过 MySQL 支持这种 SQL 语句，这个查询语句可以顺利执行。这是因为，MySQL 在执行查询语句时，会先对 SELECT 子句里的列表进行扫描，并对列进行计算。

1.3 进行不同条件的统计

本例与1.1较为相似，相当于将原始表变成了交叉表。

问题：
我们在 sample1_2 的基础上添加了“性别”列，得到了如下表格sample1_3：

pref_name（县名）	sex（性别）	population（人口）
德岛	1	60
德岛	2	40
香川	1	100
香川	2	100
爱媛	1	100
爱媛	2	50
高知	1	100
高知	2	100
福冈	1	100
福冈	2	200
佐贺	1	20
佐贺	2	80
长崎	1	125
长崎	2	125
东京	1	250
东京	2	150

现在要求按照性别、县名汇总的人数，得到如下表所示的结果：

县名	男	女
德岛	60	40
香川	100	100
爱媛	100	50
高知	100	100
福冈	100	200
佐贺	20	80
长崎	125	125
东京	250	150

解决方法：

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 sample1_3
GROUP BY pref_name;

1.4 CHECK 约束

在 CHECK 约束里对 CASE 表达式使用的较多，CHECK 约束是指约束表中某一个或者某些列中可接受的数据值或者数据格式（用于限制列中的值的范围）。

例如，某公司规定“女性员工的工资必须在 20 万日元以下”，这条规定使用 CHECK 约束来描述，对应的代码如下：

-- 蕴含式（conditional）逻辑表达式
-- 如果是女性员工，则工资是 20 万日元以下（如果不是女性员工，则无需考虑该约束）
CONSTRAINT check_salary CHECK
	( CASE WHEN sex = '2'
		   THEN CASE WHEN salary <= 200000
					 THEN 1 ELSE 0 END
		   ELSE 1 END = 1 )

然而，MySQL 所有的存储引擎均不支持 CHECK 约束，MySQL会对 CHECK 子句进行分析，但是在插入数据时会忽略，因此 CHECK 并不起作用，因此实现对数据约束有两种方法：

• 在 MySQL 中约束，如使用 ENUM 类型（接受最多64 K个串组成的一个预定义集合（ENUM(<值1>, <值2>, …)）的某个串）或者触发器等。
• 在应用程序里面对数据进行检查再插入。

1.5 UPDATE 语句中的条件分支

问题1：
假设有如下表格sample1_5_1：

name	salary
相田	300,000
神崎	270,000
木村	220,000
齐藤	290,000

现在需要根据以下条件对上表的数据进行更新：

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

得到如下表所示的结果：

name	salary
相田	270,000
神崎	324,000
木村	220,000
齐藤	290,000

解决方法：
如果按照 UPDATE 语句逐条更新数据会出现问题：工资 30 万日元的员工先降为 27 万，再升为 32.4 万日元。即使更改了更新次序，也会使得其他工资的员工在更新时发生问题。

可以在 UPDATE 语句中使用 CASE 表达式即可解决这一问题：

UPDATE sample1_5_1
   SET salary = CASE WHEN salary >= 300000
					 THEN salary * 0.9
					 WHEN salary >= 250000 AND salary < 280000
					 THEN salary * 1.2
					 ELSE salary END;

问题2：
假设有如下表格sample1_5_2：

p_key	col_1（第 1 列）	col_2（第 2 列）
a	1	一
b	2	二
c	3	三

现要求完成列 p_key 中 a 和 b 的值调换，得到如下结果：

p_key	col_1（第 1 列）	col_2（第 2 列）
b	1	一
a	2	二
c	3	三

解决方法：

UPDATE sample1_5_2
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');

注意，对于 MySQL 来说，p_key 列不能设置主键约束或者唯一键约束，否则会因键值重复而出现错误。

1.6 表之间的数据匹配

问题：
假设有如下表格sample1_6_1：

course_ id	course_ name
1	会计入门
2	财务知识
3	簿记考试
4	税务师

和表格sample1_6_2：

month	course id
200706	1
200706	3
200706	4
200707	4
200708	2
200708	4

现要求根据这两张表生成如下结果的交叉表，以方便了解每个月开设的课程：

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

解决方法：

-- 使用 IN 谓词
SELECT course_name, 
       (CASE WHEN course_id in (SELECT course_id 
								FROM sample1_6_2 
								WHERE month='200706') 
			 THEN '○' ELSE '×' END) AS '6月',
       (CASE WHEN course_id in (SELECT course_id 
								FROM sample1_6_2 
								WHERE month='200707') 
			 THEN '○' ELSE '×' END) AS '7月',
       (CASE WHEN course_id in (SELECT course_id 
								FROM sample1_6_2 
								WHERE month='200708') 
			 THEN '○' ELSE '×' END) AS '8月',
FROM sample1_6_1;

-- 使用 EXISTS 谓词
SELECT t1.course_name, 
       (CASE WHEN EXISTS (SELECT course_id 
						  FROM sample1_6_2 t2
					 	  WHERE month='200706'
						  AND t2.course_id = t1.course_id) 
			 THEN '○' ELSE '×' END) AS '6月',
       (CASE WHEN EXISTS (SELECT course_id 
						  FROM sample1_6_2 t2
					 	  WHERE month='200707'
						  AND t2.course_id = t1.course_id) 
			 THEN '○' ELSE '×' END) AS '7月',
       (CASE WHEN EXISTS (SELECT course_id 
						  FROM sample1_6_2 t2
					 	  WHERE month='200708'
						  AND t2.course_id = t1.course_id) 
			 THEN '○' ELSE '×' END) AS '8月',
FROM sample1_6_1 t1;

使用 IN 和 EXISTS 谓词得到的结果是一样的，但从性能方面来说 EXISTS 更好。通过 EXISTS 进行的子查询能够用到“month, course_id”这样的主键索引，尤其是当表 sample1_6_2 里数据比较多的时候更有优势。

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

问题：
假设有如下显示学生及其所加入社团的表格sample1_7：

std_id( 学号 )	club_id( 社团 ID)	club_name(社团名)	main_club_flg(主社团标志)
100	1	棒球	Y
100	2	管弦乐	N
200	2	管弦乐	N
200	3	羽毛球	Y
200	4	足球	N
300	4	足球	N
400	5	游泳	N
500	6	围棋	N

这张表的主键是“学号、社团 ID”，存储了学生和社团之间多对多的关系。有的学生同时加入了多个社团，有的学生只加入了某一个社团。对于加入了多个社团的学生，我们通过将其“主社团标志”列设置为 Y 或者 N 来表明哪一个社团是他的主社团；对于只加入了一个社团的学生，我们将其“主社团标志”列设置为 N。

现在要求获取只加入了一个社团的学生及社团 ID 或者加入多个社团的学生及主社团 ID，得到如下所示的结果：

std_id	main_club
100	1
200	3
300	4
400	5
500	6

解决方法：
很容易想到的办法是，针对两种情况分别编写 SQL 语句然后再进行 UNION 联合：

-- 条件 1 ：选择只加入了一个社团的学生
SELECT std_id, MAX(club_id) AS main_club
FROM sample1_7
GROUP BY std_id
HAVING COUNT(*) = 1;
UNION
-- 条件 2 ：选择加入了多个社团的学生
SELECT std_id, club_id AS main_club
FROM sample1_7
WHERE main_club_flg =  'Y' ;

这样做确实能得到正确的结果，但需要写多条 SQL 语句。而如果使用 CASE 表达式，写一条 SQL 语句就可以了：

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 sample1_7
GROUP BY std_id;

1.8 多列数据的最大值

问题：
从多行数据里选出最大值或最小值很容易——通过 GROUP BY 子句对合适的列进行聚合操作，并使用 MAX 或 MIN 聚合函数就可以求出。那么，从多列数据里选出最大值该怎么做呢？假设有如下表格sample1_8：

key	x	y	z
A	1	2	3
B	5	5	2
C	4	7	1
D	3	3	8

现要求选取出每一行中 x、y、z 的最大值，得到如下结果：

key	greatest
A	3
B	5
C	7
D	8

解决方法：
MySQL 中存在 GREATEST 函数可以轻松实现该功能：

SELECT key, GREATEST(X, Y, Z) AS greatest
FROM sample1_8

如果不用 GREATEST 函数而是用 CASE 表达式，可以实现如下：

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 
FROM sample1_8

当增加到 4 列、5 列时，推荐直接使用 GREATEST 函数，当然也可以继续用 CASE 表达式来扩展，但是这样写出来的代码会因为嵌套太深而变得不易阅读。可以考虑像下面这样，先进行行列转换，然后使用 MAX 函数来求解：

SELECT key, MAX(col) AS greatest
FROM (SELECT key, x AS col FROM Greatests
	  UNION ALL
	  SELECT key, y AS col FROM Greatests
	  UNION ALL
	  SELECT key, z AS col FROM Greatests)TMP
GROUP BY key;

2. 集合运算

2.1 实现交集和差集运算

问题：
对于 SQL 中常用的集合运算符 INTERSECT、UNION、EXCEPT，MySQL 仅支持 UNION 运算符，不支持其他两个，那如何在MySQL中实现 INTERSECT 和 EXCEPT 的功能呢？

解决方法：
使用 JOIN 可以解决该问题，假设存在表 table1 和表 table2 两个表：

• 使用内联结（INNER JOIN）方法将要选取的列作为聚合键，并在 SELECT 子句中使用 DISTINCT 关键字即可实现交集运算：

SELECT DISTINCT <table1>.<col1>, <table1>.<col2>, ...
FROM <table1> INNER JOIN <table2>
ON <table1>.<col1> = <table2>.<col1> AND <table1>.<col2> = <table2>.<col2> AND ...

• 使用外联结（OUTER JOIN）方法将要选取的列作为聚合键，并在 WHERE 子句中加入 NULL 判断条件即可实现差集运算：

-- 表table1独有
SELECT <table1>.<col1>, <table1>.<col2>, ...
FROM <table1> LEFT OUTER JOIN <table2>
ON <table1>.<col1> = <table2>.<col1> AND <table1>.<col2> = <table2>.<col2> AND ...
-- 聚合建一般不允许出现NULL，只判断一个聚合建是否为NULL即可
WHERE <table2>.<col1> IS NULL;

-- 表table2独有
SELECT <table2>.<col1>, <table2>.<col2>, ...
FROM <table1> RIGHT OUTER JOIN <table2>
ON <table1>.<col1> = <table2>.<col1> AND <table1>.<col2> = <table2>.<col2> AND ...
WHERE <table1>.<col1> IS NULL;

2.2 带余除法 (division with a remainder)

问题：
集合运算中的加减乘运算在SQL中均有一定的实现方法（UNION、EXCEPT、INTERSECT、CROSS JOIN），虽然MySQL不支持 EXCEPT 和 INTERSECT 运算符，但是在本博客2.1节我们还是通过一定的方法实现了这两种运算。对于集合运算中的除法还没有介绍。集合运算中的除法通常称为关系除法。

假设有表 Phone 和表 PersonPhone如下所示：

Phone
Apple
vivo
oppo

person	phone
John	Apple
John	oppo
John	xiaomi
John	vivo
Mary	xiaomi
Mary	vivo
Mary	oppo
Lucy	Apple
Lucy	oppo
Lucy	vivo
Dean	vivo
Jack	huawei

PersonPhone 表中保存了某几个人所拥有的手机品牌。问题是，如何从该表中选取出拥有 Phone 表中所有3个手机品牌的人员。

解决方法：
如下代码可以解决这一问题。使用关联子查询，在遍历每条记录时，都会在where子句中先找出对应记录的人员所拥有的所有手机品牌，再利用差集运算计算 Phone 表排除当前人所拥有的手机品牌后，是否存在剩余，如果不存在，说明当前人员拥有 Phone 表中全部手机品牌。

SELECT DISTINCT person
FROM PersonPhone PP1
WHERE NOT EXISTS
	   (SELECT phone
		FROM Phone
		EXCEPT
		SELECT phone
		FROM PersonPhone PP2
		WHERE PP1.person = PP2.person);

由于MySQL不能使用EXCEPT运算符，因此需要重新考虑实现方法。我们首先利用内联结将表 Phone 和表 PersonPhone 联结起来，并将联结结果按照人员分组，并计算每人所拥有的手机数量。如果该人员拥有 Phone 表中的全部手机品牌，那么联结结果表中人员手机数量应该等于3。

select  t1.person
FROM 
	(SELECT PP2.person, count(*) as cnt
	FROM Phone INNER JOIN PersonPhone PP
	ON Phone.phone = PP.phone
	group by PP.person
	) t1
where cnt = (select count(*)
		     from Phone);

-- 也可以这样写，更简便
SELECT PP.person
FROM Phone INNER JOIN PersonPhone PP
ON Phone.phone = PP.phone
group by PP.person
having count(*) = (select count(*) from Phone);

3. 自连接

SQL 的连接运算根据其特征的不同，有着不同的名称，如内连接、外连接、交叉连接等。一般来说，这些连接大都是以不同的表或视图为对象进行的，但针对相同的表或相同的视图的连接也并没有被禁止。针对相同的表进行的连接被称为“自连接”（self join）。

3.1 非等值自连接

问题：
假设有如下表格sample3_1：

name（商品名称）	price（价格）
苹果	50
橘子	100
香蕉	80

现要求得到上表中商品的两两组合（无序对），结果如下：

name_1	name_2
苹果	橘子
香蕉	橘子
香蕉	苹果

解决方法：
在自连接中运用非等值连接即可解决这一问题，按字符顺序排列各商品，只与“字符顺序比自己靠前”的商品进行配对：

SELECT t1.name AS name_1, t2.name AS name_2
FROM sample3_1 t1 CROSS JOIN sample3_1 t2
WHERE t1 .name > t2.name;

想要获取 3 个以上元素的组合时，像下面这样进行扩展即可：

SELECT t1.name AS name_1, t2.name AS name_2, t3.name AS name_3
FROM sample3_1 t1 CROSS JOIN sample3_1 t2 CROSS JOIN sample3_1 t3
WHERE t1.name > t2.name AND t2.name > t3.name;

使用等号“＝”以外的比较运算符，如“<、>、<>”进行的连接称为“非等值连接”。这里将非等值连接与自连接结合使用了，因此称为“非等值自连接”。

3.2 删除重复行

问题：
假设有如下表格sample3_2：

id	name（商品名称）	price（价格）
1	苹果	50
2	橘子	100
3	橘子	100
4	橘子	100
5	香蕉	80

先要求删除其中的重复行，得到如下的结果：

id	name（商品名称）	price（价格）
1	苹果	50
4	橘子	100
5	香蕉	80

解决方法：
如果要求得到的结果中不包含主键 id，那么这个问题利用 DISTINCT 关键字即可解决：

SELECT DISTINCT name, price
FROM sample3_2

对于包含主键 id 的表，我们可以利用关联子查询或非等值连接直接删除重复行：

-- 关联子查询
DELETE FROM sample3_2 t1
WHERE id < ( SELECT MAX(t2.id)
			 FROM sample3_2 t2
			 WHERE t1.name = t2.name AND t1.price = t2.price ) ;
-- 非等值连接
DELETE FROM sample3_2 t1
WHERE EXISTS ( SELECT *
			   FROM sample3_2 t2
			   WHERE t1.name = t2.name AND t1.price = t2.price
			   AND t1.id < t2.id );

3.3 查找局部不一致的列

问题：
假设有如下表格sample3_3，主键是人名，同一家人的家庭 ID 相同：

name（姓名）	family_id（家庭 ID）	address（住址）
前田义明	100	东京都港区虎之门 3-2-29
前田由美	100	东京都港区虎之门 3-2-92
前田静香	100	东京都港区虎之门 3-2-29
加藤茶	200	东京都新宿区西新宿 2-8-1
加藤胜	200	东京都新宿区西新宿 2-8-1
福尔摩斯	300	贝克街 221B
华生	400	贝克街 221B

通常来说，同一家人应该住在同一个地方（如加藤家），但也有像福尔摩斯和华生这样不是一家人却住在一起的情况。前田家的家庭 ID 一致，但是家庭住址却因为写错而导致二人不一致。

现要求找出同一家人（同一家庭 ID）但住址却不同的记录。

解决方法：
使用非等值自连接来解决该问题，代码会非常简洁：

SELECT DISTINCT t1.name, t1.address
FROM sample3_3 t1 CROSS JOIN sample3_3 t2
WHERE t1.family_id = t2.family_id AND t1.address <> t2.address;

该问题也可以使用关联子查询进行解决，此时不再需要使用 DISTINCT 关键字：

SELECT t1.name, t1.price
FROM sample3_3 t1
WHERE EXISTS (SELECT *
			  FROM sample3_3 t2
			  WHERE t1.family_id = t2.family_id
			  AND t1.address <> t2.address
			);

3.4 排序

问题1：
假设有如下表格sample3_4_1：

name（商品名称）	price（价格）
苹果	50
橘子	100
葡萄	50
西瓜	80
柠檬	30
香蕉	50

现要求按照价格从高到低的顺序，对上表里的商品进行排序，让价格相同的商品位次也一样，而紧接着它们的商品则有两种排序方法，一种是跳过之后的位次，另一种是不跳过之后的位次。得到的结果如下：

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

解决方法：
使用窗口函数可以轻松解决该问题：

SELECT name, price
       RANK() OVER(ORDER BY price DESC) AS rank_1,
       DENSE_RANK() OVER(ORDER BY price DESC) AS rank_2
FROM sample3_4_1
ORDER BY rank_1;

使用非等值自连接实现如下：

SELECT t1.name,
	   t1.price,
	  (SELECT COUNT(t2.price)
	   FROM sample3_4_1 t2
	   WHERE t2.price > t1.price) + 1 AS rank_1,
	  (SELECT COUNT(DISTINCT t3.price)
	   FROM sample3_4_1 t3
	   WHERE t3.price > t1.price) + 1 AS rank_2
FROM sample3_4_1 t1
ORDER BY rank_1;

-- 也可以如下实现
SELECT t1.name,
	   MAX(t1.price) AS price,
	   COUNT(t2.name) +1 AS rank_1,
	   COUNT(DISTINCT t2.name) +1 AS rank_2
FROM sample3_4_1 t1 LEFT OUTER JOIN sample3_4_1 t2
	 ON t1.price < t2.price
GROUP BY t1.name
ORDER BY rank_1;

与多表之间进行的普通连接相比，自连接的性能开销更大（特别是与非等值连接结合使用的时候），因此用于自连接的列推荐使用主键或者在相关列上建立索引。

问题2：
假设有如下相对于问题1表格增加了“地区”列的表格sample3_4_2：

district（地区）	name（商品名称）	price（价格）
东北	橘子	100
东北	苹果	50
东北	葡萄	50
东北	柠檬	30
关东	柠檬	100
关东	菠萝	100
关东	苹果	100
关东	葡萄	70
关西	柠檬	70
关西	西瓜	30
关西	苹果	20

现要求按照价格由高到低的顺序进行排序，如果出现相同位次，就跳过之后的位次，最后结果如下所示：

district	name	price	rank_1
东北	橘子	100	1
东北	苹果	50	2
东北	葡萄	50	2
东北	柠檬	30	4
关东	柠檬	100	1
关东	菠萝	100	1
关东	苹果	100	1
关东	葡萄	70	4
关西	柠檬	70	1
关西	西瓜	30	2
关西	苹果	20	3

解决方法：
该问题的解决方法依旧分为窗口函数和非等值自连接两种：

--窗口函数
SELECT district, name, price
       RANK() OVER(PARTITION BY district ORDER BY price DESC) AS rank_1
FROM sample3_4_2;

-- 非等值自连接
SELECT district, name, price,
       (SELECT COUNT(*) 
        FROM sample3_4_2 t2
        WHERE t1.district = t2.district
        AND t2.price > t1.price
       ) + 1 AS rank_1
FROM sample3_4_2 t1;

SELECT t1.district, t1.name,
       MAX(t1.price) AS price,
       COUNT(t2.name) + 1 AS rank_1
FROM sample3_4_2 t1 LEFT JOIN sample3_4_2 t2
ON t1.district = t2.district AND t1.price < t2.price
GROUP BY t1.district, t1.name;

问题3：
假设有如下相对于问题1表格增加了地区空列的表格sample3_4_3：

district	name	price	ranking
东北	橘子	100
东北	苹果	50
东北	葡萄	50
东北	柠檬	30
关东	柠檬	100
关东	菠萝	100
关东	苹果	100
关东	葡萄	70
关西	柠檬	70
关西	西瓜	30
关西	苹果	20

现要求往这个列中写入对应的位次，即更新列而非添加列，最终的结果与问题2结果相同。

解决方法：

-- 窗口函数
UPDATE sample3_4_3 
SET ranking = RANK() OVER(PARTITION BY district ORDER BY price DESC);

-- 非等值自连接
UPDATE sample3_4_3 t1
SET ranking = (SELECT COUNT(*) + 1
	           FROM sample3_4_3 t2
	           WHERE t1.district = t2.district
	           AND t2.price > t1.price
	          );

注意，有些数据库可能不支持在 SET 子句中使用窗口函数，所以它们无法正确执行相应代码，可以先用一阶子查询包装一下：

-- 使用一阶子查询包装的窗口函数
UPDATE sample3_4_3 t1
SET ranking =
		   (SELECT t2.ranking
			FROM (SELECT district, name,
			      RANK() OVER(PARTITION BY district ORDER BY price DESC) AS ranking
				  FROM sample3_4_3) t2
			WHERE t2.district = t1.district
			AND t2.name = t1.name);

4. 三值逻辑和 NULL

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

4.1 NOT IN 和 NOT EXISTS 的不等价性

问题：
假设有如下表格sample4_1_1：

name（名字）	age（年龄）	city（住址）
布朗	22	东京
拉里	19	埼玉
伯杰	21	千叶

和表格sample4_1_2：

name（名字）	age（年龄）	city（住址）
齐藤	22	东京
田尻	23	东京
山田		东京
和泉	18	千叶
武田	20	千叶
石川	19	神奈川

现要求根据这两张表查询“与住在东京的 sample4_1_2 学生年龄不同的 sample4_1_1 学生”。也就是说，希望查询到的是拉里和伯杰。

解决方法：
按照如下语句使用 NOT IN 谓词将查询不到任何记录：

SELECT *
FROM sample4_1_1
WHERE age NOT IN ( SELECT age
				   FROM sample4_1_2
				   WHERE city = '东京');

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

为了得到正确的结果，需要使用 EXISTS 谓词：

SELECT *
FROM sample4_1_1 t1
WHERE NOT EXISTS ( SELECT *
				   FROM sample4_1_2 t2
				   WHERE t1.age = t2.age AND t2.city = '东京');

因为 EXISTS 谓词永远不会返回 unknown，只会返回 true 或者 false 。因此就有了 IN 和 EXISTS 可以互相替换使用，而 NOT IN 和 NOT EXISTS 却不可以互相替换的混乱现象。

4.2 限定谓词

知识点
SQL 里有 ALL 和 ANY 两个限定谓词，用法如下：

-- ANY 关键字
-- 假设 any 内部的查询语句返回的结果个数是三个，如：result1、result2、result3
select ... from ... where a > any(...);
-- 等价于
select ... from ... where a > result1 or a > result2 or a > result3;
-- 即 a 大于子查询中的任意一个即可，等同于 a 大于子查询的最小值。

-- ALL关键字
-- ALL 关键字与 any 关键字类似，只不过上面的 or 改为 and：
select ... from ... where a > all(...);
-- 等价于
select ... from ... where a > result1 and a > result2 and a > result3;
-- 即 a 大于子查询中的每一个，等同于a大于子查询的最大值。

问题：
假设有如下表格sample4_2_1：

name（名字）	age（年龄）	city（住址）
布朗	22	东京
拉里	19	埼玉
伯杰	21	千叶

和表格sample4_2_2：

name（名字）	age（年龄）	city（住址）
齐藤	22	东京
田尻	23	东京
山田	20	东京
和泉	18	千叶
武田	20	千叶
石川	19	神奈川

现要求根据这两张表查询“与住在东京的所有 sample4_2_2 学生年龄都小的 sample4_1_1 学生”。也就是说，希望查询到的是拉里。

解决方法：
可以使用 ALL 谓词进行如下实现：

SELECT *
FROM sample4_2_1
WHERE age < ALL ( SELECT age
				  FROM sample4_2_2
				  WHERE city = '东京');

但是，如果山田的年龄像4.1节那样为 NULL，导致限定谓词中出现了 NULL，那么此时返回记录为空。可以看出，限定谓词任一参数为 NULL 则结果为 NULL。

注意，限定谓词和极值函数（MAX、MIN）不是等价的，比较对象原本就不存在时（即输入为空表或空集时），ALL 限定谓词会返回所有结果，而极值函数会返回 NULL，极值函数会自动忽略 NULL。实际上，当输入为空表时返回 NULL 的不只是极值函数， COUNT 以外的聚合函数也是如此。

5. HAVING子句

WHERE 子句用来调查集合元素的性质，而 HAVING 子句用来调查集合本身的性质。

如果实体对应的是表中的一行数据，那么该实体应该被看作集合中的元素，因此指定查询条件时应该使用 WHERE 子句。如果实体对应的是表中的多行数据，那么该实体应该被看作集合，因此指定查询条件时应该使用 HAVING 子句。

5.1 寻找缺失的编号

问题1：
假设有如下带有“连续编号”列的表格sample5_1_1：

seq（连续编号）	name（名字）
1	迪克
2	安
3	莱露
5	卡
6	玛丽
8	本

现要求查询这张表里“连续编号”列是否存在数据缺失。

解决方法：
对于这种起始编号为1的列，只需计算用 COUNT(*) 统计出来的行数是否等于“连续编号”列的最大值，即可判断编号是否缺失：

SELECT '存在缺失的编号' AS gap
FROM sample5_1_1
HAVING COUNT(*) <> MAX(seq);

HAVING 子句是可以单独使用的，可以认为是对空字段进行了 GROUP BY 操作，只不过省略了 GROUP BY 子句。

进一步，可以像下面这样查出来缺失编号的最小值：

SELECT MIN(seq + 1) AS gap
FROM sample5_1_1
WHERE (seq+ 1) NOT IN ( SELECT seq FROM sample5_1_1);

• 如果没有缺失的编号，则查询到的结果是最大编号的下一个编号。
• 如果表 sample5_1_1 里包含 NULL ，那么这条 SQL 语句的查询结果为空。
• 这个查询不够周全，并不能涵盖所有情况。例如，如果表里没有编号 1，那么缺失编号的最小值应该是 1，但是这条 SQL 语句不能得出正确的结果。

问题2：
考虑一种更一般的情况，“连续编号”列起始编号不一定为1，假设有如下表格sample5_1_2：

seq（连续编号）	name（名字）
2	迪克
3	安
4	莱露
5	卡
6	玛丽
8	本

现仍旧要求查询上表中“连续编号”列是否存在数据缺失。

解决方法：
如果数列的最小值和最大值之间没有缺失的编号，它们之间包含的元素的个数应该是“最大值－最小值＋ 1”：

SELECT '存在缺失的编号' AS gap
FROM sample5_1_2
HAVING COUNT(*) <> MAX(seq) - MIN(seq) + 1 ;

如果不论是否存在缺失的编号，都想要返回结果，那么只需要把条件写到 SELECT 里即可：

SELECT CASE WHEN COUNT(*) = 0
			THEN '表为空'
			WHEN COUNT(*) <> MAX(seq) - MIN(seq) + 1
			THEN '存在缺失的编号'
			ELSE '连续' END AS gap
FROM sample5_1_2;

查找最小的缺失编号的 SQL 语句改进如下：

SELECT CASE WHEN COUNT(*) = 0 OR MIN(seq) > 1 THEN 1
			ELSE (SELECT MIN(seq +1)
				  FROM sample5_1_2 S1
				  WHERE NOT EXISTS
					   (SELECT *
						FROM sample5_1_2 S2
						WHERE S2.seq = S1.seq + 1)) END
FROM sample5_1_2;

5.2 求众数和中位数

问题：
假设有如下表格sample5_2：

name（名字）	income（收入）
桑普森	400000
迈克	30000
怀特	20000
阿诺德	20000
史密斯	20000
劳伦斯	15000
哈德逊	15000
肯特	10000
贝克	10000
斯科特	10000

对该表格求取平均值并不能很好地反正其中人们的收入水平，因为该表格中存在离群点。这种时候需要使用更能准确反映出群体趋势的指标——众数和中位数。

解决方法：
求众数的思路很简单，就是将收入相同的人汇总到一个集合里，然后从汇总后的各个集合里找出元素个数最多的集合：

-- 使用限定谓词
SELECT income, COUNT(*) AS cnt
FROM sample5_2
GROUP BY income
HAVING COUNT(*) >= ALL(SELECT COUNT(*)
					   FROM sample5_2
					　　GROUP BY income);
-- 极值函数
SELECT income, COUNT(*) AS cnt
FROM sample5_2
GROUP BY income
HAVING COUNT(*) >= (SELECT MAX(cnt)
					FROM (SELECT COUNT(*) AS cnt
						  FROM sample5_2
						  GROUP BY income) TMP) ;

注意：

• 不能直接将 ALL 换成 MAX，MAX是行方向取最大值，不是列方向。
• 子查询中 FROM 子句必须起别名，否则 MySQL 会报错。

求中位数的思路是：将集合里的元素按照大小分为上半部分 S1 和下半部分 S2 两个子集，同时让这 2 个子集共同拥有集合正中间的元素。这样，共同部分的元素的平均值就是中位数。代码实现如下：

-- 非等值自连接
SELECT AVG(DISTINCT income)
FROM (SELECT T1.income
	  FROM sample5_2 T1 CROSS JOIN sample5_2 T2
	  GROUP BY T1.income
	  HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) >= COUNT(*)/2
	  AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) >= COUNT(*)/2 ) TMP;

上述 SQL 语句的要点在于比较条件>= COUNT(*)/2里的等号，加上等号并不是为了清晰地分开子集 S1 和 S2，而是为了让这 2 个子集拥有共同部分。如果去掉等号，将条件改成> COUNT(*)/2，那么当元素个数为偶数时，S1 和 S2 就没有共同的元素了，也就无法求出中位数了。此外，外层的 AVG 函数也是考虑了元素个数为偶数的情况。

5.3 查询不包含 NULL 的集合（特征函数）

问题：
假设有如下表格sample5_3：

student_id（学号 ID）	dpt（学院）	sbmt_date（提交日期）
100	理学院	2005-10-10
101	理学院	2005-09-22
102	文学院
103	文学院	2005-09-10
200	文学院	2005-09-22
201	工学院
202	经济学院	2005-09-25

学生提交报告后，“提交日期”列会被写入日期，而提交之前是 NULL 。现在要求从这张表里找出哪些学院的学生全部都提交了报告（即理学院、经济学院）。

解决方法：

-- 使用 COUNT 函数
SELECT dpt
FROM sample5_3
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);

-- 使用 CASE 表达式
SELECT dpt
FROM sample5_3
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END);

上述语句中，CASE 表达式的作用相当于进行判断的函数，用来判断各个元素（ 行）是否属于满足了某种条件的集合，这样的函数称为特征函数（characteristic function）。

5.4 精确关系除法

问题：
假设有如下表格sample5_4_1：

item（商品）
啤酒
纸尿裤
自行车

和表格sample5_4_2：

shop（店铺）	item（商品）
仙台	啤酒
仙台	纸尿裤
仙台	自行车
仙台	窗帘
东京	啤酒
东京	纸尿裤
东京	自行车
大阪	电视
大阪	纸尿裤
大阪	自行车

现要求查询在表 sample5_4_2 中包含且仅包含表 sample5_4_1 中所有商品的店铺，即东京店铺。这类问题被称为“精确关系除法”（exact relational division），即只选择没有剩余商品的店铺。

解决方法：
类比 2.2 节，可以轻松实现查询在表 sample5_4_2 中包含了（不要求仅包含）表 sample5_4_1 中所有商品的店铺：

SELECT t2.shop
FROM sample5_4_2 t2 INNER JOIN sample5_4_1 t1
ON t2.item = t1.item
group by t2.shop
having count(*) = (select count(*) from sample5_4_1);

使用外连接即可解决精确关系除法：

SELECT t2.shop
FROM sample5_4_2 t2 LEFT JOIN sample5_4_1 t1
ON t2.item = t1.item
group by t2.shop
having count(t2.item) = (select count(*) from sample5_4_1)
AND count(t1.item) = (select count(*) from sample5_4_1);

5.5 全称量化和存在量化的转换

问题：
假设有如下表格sample5_5：

member（队员）	team_id（队伍编号 ID）	status（状态）
乔	1	待命
肯	1	出勤中
米克	1	待命
卡伦	2	出勤中
凯斯	2	休息
简	3	待命
哈特	3	待命
迪克	3	待命
贝斯	4	待命
阿伦	5	出勤中
罗伯特	5	休息
卡根	5	待命

现要求查询当前可以出勤的队伍，可以出勤即队伍里所有队员都处于“待命”状态。

解决方法：
“所有队员都处于‘待命’状态”这个条件是全称量化命题，可以转换为存在量化命题——“不存在不处于待命状态的队员”，因此可以用 NOT EXISTS 来表达：

SELECT team_id, member
FROM sample5_5 T1
WHERE NOT EXISTS
		(SELECT *
		 FROM sample5_5 T2
		 WHERE T1.team_id = T2.team_id AND status <> '待命');

使用 HAVING 子句实现非常简单：

SELECT team_id
FROM sample5_5 
GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN status = '待命' THEN 1 ELSE 0 END);

-- 也可以这么写，前提是 status 列没有 NULL
SELECT team_id
FROM sample5_5 
GROUP BY team_id
HAVING MAX(status) = '待命' AND MIN(status) = '待命'; 

也可以把条件放在 SELECT 子句里，以列表形式显示出各个队伍是否所有队员都在待命：

SELECT team_id,
	   CASE WHEN MAX(status) = '待命' AND MIN(status) = '待命'
			THEN '全都在待命'
			ELSE '队长！人手不够' END AS status
FROM sample5_5 
GROUP BY team_id;

5.6 存在重复数据的集合——多重集合

问题1：
假设有如下表格sample5_6_1：

center（生产地）	receive_date（入库日期）	material（材料）
东京	2007-4-01	锡
东京	2007-4-12	锌
东京	2007-5-17	铝
东京	2007-5-20	锌
大阪	2007-4-20	铜
大阪	2007-4-22	镍
大阪	2007-4-29	铅
名古屋	2007-3-15	钛
名古屋	2007-4-01	钢
名古屋	2007-4-24	钢
名古屋	2007-5-02	镁
名古屋	2007-5-10	钛
福冈	2007-5-10	锌
福冈	2007-5-28	锡

现要求查询重复入库过同一材料的生产地，即东京和名古屋。

解决方法：
用 HAVING 子句实现起来很简单：

SELECT center
FROM sample5_6_1
GROUP BY center
HAVING COUNT(material) <> COUNT(DISTINCT material);

也可以通过将 HAVING 改写成 EXISTS 的方式来解决该问题，用 EXISTS 改写后的 SQL 语句也能够查出重复的具体是哪一种材料：

SELECT center, material
FROM sample5_6_1 M1
WHERE EXISTS
	   (SELECT *
		FROM sample5_6_1 M2
		WHERE M1.center = M2.center
		AND M1.receive_date <> M2.receive_date
		AND M1.material = M2.material);

相反，如果想要查出不存在重复材料的生产地及材料有哪些，只需要把 EXISTS 改写为 NOT EXISTS 就可以了。

整理一下在调查集合性质时经常用到的条件:

Nov	条件表达式	用途
1	COUNT (DISTINCT col) = COUNT (col)	col 列没有重复的值
2	COUNT(*) = COUNT(col)	col 列不存在 NULL
3	COUNT(*) = MAX(col)	col 列是连续的编号（起始值是 1）
4	COUNT(*) = MAX(col) - MIN(col) + 1	col 列是连续的编号（起始值是任意整数）
5	MIN(col) = MAX(col)	col 列都是相同值，或者是 NULL
6	MIN(col) * MAX(col) > 0	col 列全是正数或全是负数
7	MIN(col) * MAX(col) < 0	col 列的最大值是正数，最小值是负数
8	MIN(ABS(col)) = 0	col 列最少有一个是 0
9	MIN(col - 常量 ) = - MAX(col - 常量 )	col 列的最大值和最小值与指定常量等距

问题2：
在问题1中我们只针对 material 一个字段检查了集合中是否存在重复值，现在考虑针对多个字段检查是否存在重复。

在表 sample5_6_1 的基础上添加“原产国”字段，得到下表 sample5_6_2：

center( 生产地 )	receive_date（入库日期）	material（材料）	orgland( 原产国 )
东京	2007-04-01	锡	智利
东京	2007-04-12	锌	泰国
东京	2007-05-17	铝	巴西
东京	2007-05-20	锌	泰国
大阪	2007-04-20	铜	澳大利亚
大阪	2007-04-22	镍	南非
大阪	2007-04-29	铅	印度
名古屋	2007-03-15	钛	玻利维亚
名古屋	2007-04-01	钢	智利
名古屋	2007-04-24	钢	阿根廷
名古屋	2007-05-02	镁	智利
名古屋	2007-05-10	钛	泰国
福冈	2007-05-10	锌	美国
福冈	2007-05-28	锡	俄罗斯

现要求从上表中查出材料和原产国两个字段都重复的生产地。答案只有（锌, 泰国）重复了的东京。对于名古屋，如果只看材料，那么“钢”有重复，但是因为产地分别是智利和阿根廷，所以应该被排除在外。

解决方法：
对于这一问题最先想到的解决方法是：

SELECT center
FROM sample5_6_2
GROUP BY center
HAVING COUNT(material, orgland) <> COUNT(DISTINCT material, orgland);

但是实际上，COUNT 函数的参数只能是一列，我们可以将多列拼凑成一列作为参数传递：

SELECT center
FROM sample5_6_2
GROUP BY center
HAVING COUNT(material || orgland) <> COUNT(DISTINCT material || orgland);

如果想要拼凑的字段不是字符串类型，可以先转换成字符串类型再拼凑。需要扩展成三个字段以上时，同样地拼凑起来就可以了。

下面这种解法也是错误的：

SELECT center
FROM sample5_6_2
GROUP BY center
HAVING COUNT(material) <> COUNT(DISTINCT material)
AND COUNT(orgland) <> COUNT(DISTINCT orgland);