举个例子:有个教师表,有两个列 id 和 教师名字; 有个学生表,有三列:姓名, 性别和教师id, 现在要查询学生全为男生的教师有哪些? (一条sql)
教师表 teacher_n
学生表 student_n
思路:
学生表中sex=1代表男生,sex=0代表女生
看上图我们发现老师的学生有的是男生,有的是女生,有的全为男生,
既然我们想要学生全是男生的老师,那最先想到的就是要把学生里有女生的老师剔除掉,可是,怎么剔除呢?
首先想到的是对老师进行分组,查看各老师下面的学生分布
第一步:
对老师进行分组,然后对该老师下面的学生分性别计数
SELECT
t_id,
count( CASE WHEN a.sex = 1 THEN id ELSE NULL END ) AS man_num,
count( CASE WHEN a.sex = 0 THEN id ELSE NULL END ) AS woman_num
FROM
student_n AS a
GROUP BY
t_id
我们看到 t_id是4的老师的学生都是男的
第二步:
选出这个t_id是4的老师
SELECT
b.t_id
FROM
(
SELECT
t_id,
count( CASE WHEN a.sex = 1 THEN id ELSE NULL END ) AS man_num,
count( CASE WHEN a.sex = 0 THEN id ELSE NULL END ) AS woman_num
FROM
student_n AS a
GROUP BY
t_id
) AS b
WHERE
b.woman_num =0
第三步:
我们拿到了符合条件的老师的id,然后匹配到出这个老师的名字
select *
from teacher_n
where t_id
in (
SELECT
b.t_id
FROM
(
SELECT
t_id,
count( CASE WHEN a.sex = 1 THEN id ELSE NULL END ) AS man_num,
count( CASE WHEN a.sex = 0 THEN id ELSE NULL END ) AS woman_num
FROM
student_n AS a
GROUP BY
t_id
) AS b
WHERE
b.woman_num =0)
做到这里,我们已经成功提取出符合条件的老师名字,但是还有没有可以改进的呢?
当然有了,第三步中我们用到的是in ,由于样本量很小,所以这样做没什么影响,但是当数据量很大时,会严重影响查询效率。因为每次提取一个t_id,都会遍历一次全表。
所以一般情况下,我们都是使用join来优化
第四步:
SELECT
c.t_name
FROM
teacher_n AS c
INNER JOIN (
SELECT
b.t_id AS t_id
FROM
(
SELECT
t_id,
count( CASE WHEN a.sex = 1 THEN id ELSE NULL END ) AS man_num,
count( CASE WHEN a.sex = 0 THEN id ELSE NULL END ) AS woman_num
FROM
student_n AS a
GROUP BY
t_id
) AS b
WHERE
b.woman_num = 0
) AS d ON c.t_id = d.t_id
从原来的0.031s缩短到了0.018s.
思路二:
我们知道,老师带学生一共有三种情况,老师只带了男学生,只带女学生,带的学生中有男有女。
我们怎么判定老师只带了男学生呢?
老师带了学生,老师只带男学生,如果我们找到老师带的学生人数,又找出男生人数,这两个人数相当等,那我们就可以找出符合条件的老师
第一步:找出每个老师带了多少学生
SELECT
t_id,
count( sex ) AS num
FROM
student_n
GROUP BY
t_id
第二步:找出每个老师下面带了多少个男学生
SELECT
t_id,
count( sex ) AS num
FROM
student_n
WHERE
sex = 1
GROUP BY
t_id
第三步:将上面两个条件合并,确定符合条件的老师id
SELECT
a.t_id
FROM
( SELECT t_id, count( sex ) AS num FROM student_n WHERE sex = 1 GROUP BY t_id ) AS a
INNER JOIN ( SELECT t_id, count( sex ) AS num FROM student_n GROUP BY t_id ) AS b ON a.t_id = b.t_id
WHERE
a.num = b.num