SQL-查询学生全为男生的老师为哪些

举个例子：有个教师表，有两个列 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

第四步：将上面的id与teacher_n表连接，得到老师名字