exists用法，查询缺席的人

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
-- 原数据
+-----------+--------+
| meeting   | person |
+-----------+--------+
| 第一次     | 张三    |
| 第一次     | 李四    |
| 第一次     | 王五    |
| 第二次     | 张三    |
| 第二次     | 赵六    |
| 第三次     | 李四    |
| 第三次     | 王五    |
| 第三次     | 赵六    |
+-----------+--------+
-- 预期结果
+-----------+--------+
| meeting   | person |
+-----------+--------+
| 第三次     | 张三   |
| 第二次     | 李四   |
| 第二次     | 王五   |
| 第一次     | 赵六   |
+-----------+--------+
-- 可以用假设所有人都参加减去实际参加得到
-- 所有参加的情况可通过交叉链接得到
select distinct m1.meeting, m2.person from meetings as m1 cross join meetings as m2;
-- 再用not exist减去实际参加的人
select distinct m1.meeting,m2.person from meetings as m1 
cross join meetings as m2 where not exists 
(select * from meetings m3 where m1.meeting = m3.meeting and m2.person = m3.person);

肯定转双重否定

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
-- 查出所有科目成绩都在50分以上的学生
+------------+---------+-------+
| student_id | subject | score |
+------------+---------+-------+
|          1 | 数学    |   100 |
|          1 | 语文    |    80 |
|          1 | 理化    |    80 |
|          2 | 数学    |    80 |
|          2 | 语文    |    95 |
|          3 | 数学    |    40 |
|          3 | 语文    |    90 |
|          3 | 社会    |    55 |
|          4 | 数学    |    80 |
+------------+---------+-------+
-- 直接写法
select student_id from test_scores group by student_id having sum(case when score>50 then 1 else 0 end)=count(*);
-- 转化成双重否定，没有一个科目不满50分
-- 所有学生减去有低于50分的学生
select distinct student_id from test_scores as t1 where not exists (
	select * from test_scores as t2 where t1.student_id=t2.student_id and t2.score<50
);

-- 找出数学成绩不低于80，语文成绩不低于50，必须同时拥有语文和数学成绩
-- 直接写法
select student_id from test_scores where 1 = case 
when subject = '语文' and score >= 50 then 1 
when subject = '数学' and score >= 80 then 1 else 0 end group by student_id having count(*) = 2;

-- 双重否定写法，减法运算，注意subject in
select student_id from test_scores as t1 where subject in ("数学", "语文") and 
not exists (
	select * from test_scores as t2 where 
	t1.student_id=t2.student_id and 1 = case 
	when subject = '数学' and score < 80 then 1 
	when subject = '语文' and score < 50 then 1 else 0 end
) 
group by student_id having count(*) = 2;

having与not exists的优缺点比较
having逻辑易理解
not exists不易理解但利用到索引性能好

查询列全是null的记录

1
2
3
4
5
6
7
8
9
10
+------+------+------+------+------+------+------+
| key  | col1 | col2 | col3 | col4 | col5 | col6 |
+------+------+------+------+------+------+------+
| A    | NULL | NULL | NULL | NULL | NULL | NULL |
| B    |    3 | NULL | NULL | NULL | NULL | NULL |
| C    |    1 |    1 |    1 |    1 |    1 |    1 |
| D    | NULL | NULL |    9 | NULL | NULL | NULL |
| E    | NULL |    3 | NULL |    1 |    9 |    9 |
+------+------+------+------+------+------+------+
select * from array where coalesce(col1, col2, col3, col4, col5, col6) is null;

生成数字0-99

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- digit table
+------+
| val  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    9 |
|    0 |
+------+
select d1.val + (d2.val * 10) as seq from digit as d1 cross join digit d2 order by seq;

生成0-799(三位数)

1
2
3
4
select d1.val + (d2.val * 10) + (d3.val * 100) as seq 
from digit as d1 cross join digit d2 cross join digit d3 
where d1.val + (d2.val * 10) + (d3.val * 100) < 800 
order by seq;

求全部缺失的编号

1
2
3
4
5
-- 利用上述结果生成视图，视图的结果减去表中已有编号即为缺失编号
create view digit_view as select d1.val + (d2.val * 10) as seq from digit as d1 cross join digit d2 order by seq;

-- not in 做减法
select * from digit where val not in (select * from digit_view);