发现自己不知道的越来越多了,终究还是自己太菜了
Exists是什么
说到exists,先来讲讲什么是谓词把,在SQL的很多保留字中,有很多都被归为谓词一类,可以理解为,谓词就是返回值为真值。例如"=,<,>"以及"between and like ,in ,null, is not null",在写SQL语句的时候,我们几乎每天都在用谓词。就像上面说的,就是一种特殊的函数,返回值是真值。
谓词的概念说清了,下面来讲讲exists.
在平常的sql中,博主用到的exists其实并不多,很少用。就像之前博主写的关联子查询,几乎就没听说过,更别说用了。
下面通过一个例子来讲解一下
查询表中“不”存在的数据
找出没有参加会议的人
假设所有人都参加了会议 -- 将这个生成一个集合
没有参加会议的人 = 所有人参加会议 - 实际上参加会议的人
- 所有人都参加了会议
select dietinct M1.meeting,M2.person
from Meetings M1,Meetings M2
2.实际上参加会议的人
select * from Meetings
3.没有参加会议的人
select distinct M1.meeting,M2.person
from Meetings M1,Meetings M2
where not exists
(select * from Meetings M3 where M3.meeting = M1.meeting and M3.person = M2.person)
分析一下上面的SQL,在全部参加会议的表里面找到不存在参加会议的人。
可能会有观众不了解,为什么是not exists?如果去掉not呢
很明显的知道.
去掉 not之后就和原表的数据一模一样了。那么加上not 不就是
所有参加的人 - 实际参加的人 = 没有参加的人
可能光看一个例子并看不懂,下面再来一个例子大家就懂了。
下面是一张学习成绩表的数据
需求:找到所有科目都在50分以上的同学。
从上面的表中可以看到,所有科目分数都在50分以上的同学有3人。分别是 100,200,400
要找到所有科目都在50分以上的同学,换一个思路
所有科目的分数都没有在50一下
这样,我们就有了sql
select distinct student_id
from TestScore T1
where not exists
(select * from TestScore T2 where T1.student_id = T2.student_id and T2.score < 50 )
需求2:数学的分数要在80分以上,语文的分数在50分以上的同学
如果科目是数学,那么数学的分数要在80以上
如果科目是语文,那么语文的分数要在50以上
使用case when来解决这道题
select distinct student_id from TestScore T1
where subject in ("数学","语文")
and not exists
( select * from TestScore 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)
解释一下上面的代码
首先我们要找到语文和数学这两门成绩
排除掉 语文少于50分的,数学少于80分的,剩下的就是我们要求的结果
先看一下结果
但是我们发现,结果并不是正确的,因为在元数据中,学号为400的同学,只有数学成绩,没有语文成绩,那么如何排除掉这个学号为400的同学呢?肯定不是 where student_id != 400 。因为我们的数据量小,所以我们通过对比元数据就可以看到,学号为400的同学没有语文成绩。
题目要求是数学和语文成绩。两门学科,我们可以通过学科数目来排除掉这个400
select distinct student_id
from TestScore T1
where subject in ("数学","语文")
and not exists
( select * from TestScore 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
根据学号分组,这样整体的数据就是下面的格式
学号 科目
100 数学,语文
200 数学,语文
400 数学
然后通过Having来排除掉只有一条数据的400
真正的结果就应该是下面的样子
总结:
如果我们感觉exists比In好用,那就有点以偏概全了。
我们认为in好用,是因为我们只会用IN,就像上篇博文讲的,我们不知道关联子查询,不用关联子查询,就不知道关联子查询的好。
但是当我们学习了exists的用法之后,会感觉到exists的用法其实并不比in的作用小。
只有踏出舒适区,我们才知道自己以前有多渺小。