自连接

1
2
3
4
5
-- 交叉连接（笛卡尔积），包含有序对[(xxh, xjy)和(xjy, xxh)]、左右相同值(xxh, xxh)
select u1.name,u2.name from user u1, user u2;
-- 去除有序对变为无序对、去除左右相同值
select u1.name,u2.name from user u1, user u2 where u1.name > u2.name;
-- >, <, <>称为非等值连接

删除重复数据,保留小的id记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
+------+------+------+------+------+
| key  | x    | y    | z    | id   |
+------+------+------+------+------+
| A    |    1 |    2 |    3 |    1 |
| B    |    5 |    5 |    2 |    2 |
| C    |    4 |    7 |    1 |    3 |
| D    |    3 |    3 |    8 |    4 |
| D    |    3 |    3 |    8 |    5 |
+------+------+------+------+------+
delete from greatests where id not in (
	select * from 
		(select min(id) from greatests group by `key`,x,y,z) as g	
);

不使用窗口函数实现排名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select u1.name,u1.money,(
	select count(u2.money) from user u2 where u2.money > u1.money
) + 1 as ranking from user as u1 order by money desc;
-- 输出
+------+---------+---------+
| name | money   | ranking |
+------+---------+---------+
| xxh6 | 3500.00 |       1 |
| xxh4 | 3000.00 |       2 |
| xxh5 | 2500.00 |       3 |
| xxh3 | 2000.00 |       4 |
| 111  | 1999.00 |       5 |
| xxh2 | 1500.00 |       6 |
| xxh  | 1000.00 |       7 |
+------+---------+---------+
-- 这里第二个select选出比自己的money大的学生数，xxh6没有比自己大的count返回0，+1返回1

布尔类型优先级：true > null > false

1
2
3
true or null -> true
true and null -> true
false or null -> null

case和null

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 无法返回null,when null相当于where gender = null,gender=null返回null，无法查询出数据
select 
case gender 
	when 1 then '男'
	when null then '未知'
end
from user;
-- 改写方法
select
	case when gender = 1 then '男'
	when gender is null then '未知'
end
from user;

not in和not exist

1
2
3
4
-- not in后可能含有null,可能无法查询出数据
select name from user where class not in (select id from class);
-- not exist不含有null
select name from user where not in (select * from class);

all用法

1
2
3
4
5
6
7
-- all和in类似，需要都满足
-- 选出比2班所以同学money少的同学
select name,money from user where money < all (select money from user where class=2);
-- 若all后含有null，也无法查询出数据
-- 或者使用min代替
select name,money from user where money < (select min(money) from user where class=2);
-- 若class=2无数据money<null依然无法查出数据

having使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 查询是否中间缺失数据
select '缺失' from user having count(*) <> max(id);
-- 求众数
select money,count(*) from user group by money having count(*) >= all (select count(*) from user group by money);
-- TODO: 求中位数
-- 统计每个班gender不为null的学生数
select class, count(*) as user_count from user group by class having count(*)=count(gender);
-- 或者使用case表达式
select class, count(*) as user_count from user group by class having count(*)=sum(
	case when gender is not null then 1 else 0 end
);
-- 查询包含所有商品的超市
+-----------+
| item      |
+-----------+
| 啤酒      |
| 自行车    |
+-----------+
+--------------+-----------+
| shop         | item      |
+--------------+-----------+
| 联合超市     | 啤酒      |
| 联合超市     | 自行车    |
| 联华超市     | 啤酒      |
+--------------+-----------+
select shop from shopitems group by shop having count(*)=(select count(*) from items);
-- 查询结果
+--------------+
| shop         |
+--------------+
| 联合超市     |
+--------------+

标量子查询实现转置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 原数据
+------+--------+
| name | course |
+------+--------+
| xxh  | python |
| xjy  | java   |
| xxh  | sql    |
+------+--------+
-- c0作为主表，其余为标量子查询
-- 缺点是开销大
select distinct c0.name,
(select 'O' from courses where course='sql' and name=c0.name) as 'sql',
(select 'O' from courses where course='python' and name=c0.name) as 'python',
(select 'O' from courses where course='java' and name=c0.name) as 'java'
from courses as c0;
-- 结果
+------+------+--------+------+
| name | sql  | python | java |
+------+------+--------+------+
| xxh  | O    | O      | NULL |
| xjy  | NULL | NULL   | O    |
+------+------+--------+------+

使用case替代标量子查询实现转置

1
2
3
4
5
6
select 
name,
case when sum(case when course='sql' then 1 else 0 end) = 1 then 'O' end as 'sql',
case when sum(case when course='java' then 1 else 0 end) = 1 then 'O' end as 'java',
case when sum(case when course='python' then 1 else 0 end) = 1 then 'O' end as 'python'
from courses group by name;

使用外连接把多列汇总为一列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 原数据
+----------+--------+--------+--------+
| employee | child1 | child2 | child3 |
+----------+--------+--------+--------+
| 张三     | 小白   | 小明   | 小黑   |
| 李四     | 小丽   | 夏子   | NULL   |
| 王五     | 夏子   | NULL   | NULL   |
| 赵六     | NULL   | NULL   | NULL   |
+----------+--------+--------+--------+
-- 预期结果
+----------+--------+
| employee | child  |
+----------+--------+
| 张三     | 小白   |
| 李四     | 小丽   |
| 李四     | 夏子   |
| 王五     | 夏子   |
| 张三     | 小明   |
| 张三     | 小黑   |
| 赵六     | NULL   |
+----------+--------+
-- 所有孩子的视图
create view children(child) as 
select child1 from personnel
union
select child2 from personnel
union 
select child3 from personnel;

select p.employee, c.child from personnel p left join children as c on c.child in (p.child1, p.child2, p.child3);

使用外连接做嵌套式表侧栏

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
-- 原数据
+-----------+-----------+
| age_class | age_range |
+-----------+-----------+
|         1 | 21-30岁   |
|         2 | 31-40岁   |
|         3 | 41-50岁   |
+-----------+-----------+
+--------+-----+
| sex_cd | sex |
+--------+-----+
| m      | 男  |
| f      | 女  |
+--------+-----+
+-----------+-----------+--------+------------+
| pref_name | age_class | sex_cd | population |
+-----------+-----------+--------+------------+
| 秋田      |         1 | m      |        400 |
| 秋田      |         3 | m      |       1000 |
| 秋田      |         1 | f      |        800 |
| 秋田      |         3 | f      |       1000 |
| 青森      |         1 | m      |        700 |
| 青森      |         1 | f      |        500 |
| 青森      |         3 | f      |        800 |
| 东京      |         1 | m      |        900 |
| 东京      |         1 | f      |       1500 |
| 东京      |         3 | m      |        900 |
| 千叶      |         1 | m      |        900 |
| 千叶      |         1 | f      |       1000 |
| 千叶      |         3 | f      |        900 |
+-----------+-----------+--------+------------+
-- 预期结果
+-----------+--------+-----------+--------+--------+--------+
| age_class | sex_cd | age_class | sex_cd | 东北   | 关东   |
+-----------+--------+-----------+--------+--------+--------+
|         1 | m      |         1 | m      |   1100 |   1800 |
|         1 | f      |         1 | f      |   1300 |   2500 |
|         2 | m      |      NULL | NULL   |   NULL |   NULL |
|         2 | f      |      NULL | NULL   |   NULL |   NULL |
|         3 | m      |         3 | m      |   1000 |    900 |
|         3 | f      |         3 | f      |   1800 |    900 |
+-----------+--------+-----------+--------+--------+--------+
-- 先按年龄阶层和性别做分组查询
select 
age_class, sex_cd,
sum(case when pref_name in ('青森', '秋田') then population else 0 end) as '东北', 
sum(case when pref_name in ('东京', '千叶') then population else 0 end) as '关东' 
from `TblPop` group by age_class, sex_cd;
+-----------+--------+--------+--------+
| age_class | sex_cd | 东北   | 关东   |
+-----------+--------+--------+--------+
|         1 | f      |   1300 |   2500 |
|         1 | m      |   1100 |   1800 |
|         3 | f      |   1800 |    900 |
|         3 | m      |   1000 |    900 |
+-----------+--------+--------+--------+
-- 再拿查询后的结果和年龄阶层表和性别表做表链接
select * from ..... as p
left join TblSex as s on s.sex_cd=p.sex_cd left join TblAge as a on a.age_class=p.age_class;
+-----------+--------+--------+--------+--------+------+-----------+-----------+
| age_class | sex_cd | 东北   | 关东   | sex_cd | sex  | age_class | age_range |
+-----------+--------+--------+--------+--------+------+-----------+-----------+
|         1 | m      |   1100 |   1800 | m      | 男   |         1 | 21-30岁   |
|         1 | f      |   1300 |   2500 | f      | 女   |         1 | 21-30岁   |
|         3 | m      |   1000 |    900 | m      | 男   |         3 | 41-50岁   |
|         3 | f      |   1800 |    900 | f      | 女   |         3 | 41-50岁   |
+-----------+--------+--------+--------+--------+------+-----------+-----------+
-- 但是没有出现age_class=2的数据，因为TblPop没有age_class=2的数据
-- 若想展示age_class=2的数据，可先让TblAge与TblSex做交叉连接，再与TblPop连接
select * from (select age_class, sex_cd from TblSex cross join TblAge) as master left join (select 
age_class, sex_cd,
sum(case when pref_name in ('青森', '秋田') then population else 0 end) as '东北', 
sum(case when pref_name in ('东京', '千叶') then population else 0 end) as '关东' 
from `TblPop` group by age_class,sex_cd ) as p on p.age_class=Master.age_class and p.sex_cd=Master.sex_cd;
+-----------+--------+-----------+--------+--------+--------+
| age_class | sex_cd | age_class | sex_cd | 东北   | 关东   |
+-----------+--------+-----------+--------+--------+--------+
|         1 | m      |         1 | m      |   1100 |   1800 |
|         1 | f      |         1 | f      |   1300 |   2500 |
|         2 | m      |      NULL | NULL   |   NULL |   NULL |
|         2 | f      |      NULL | NULL   |   NULL |   NULL |
|         3 | m      |         3 | m      |   1000 |    900 |
|         3 | f      |         3 | f      |   1800 |    900 |
+-----------+--------+-----------+--------+--------+--------+