mysql is null没有查询到空_mysql 查询没有学全所有课程的同学的信息

表信息

student 表

score表

课程表

-- 查询没有学全所有课程的同学的信息

我的方法：

（先将所有课程信息连接到一起，再筛选课程有空值的学生）

# 第一步 将所有课程信息连接到一起
select s.*,sc1.score,sc2.score,sc3.score from student s 
left join score sc1 on s.s_id = sc1.s_id and sc1.c_id = '01'
left join score sc2 on s.s_id = sc2.s_id and sc2.c_id = '02'
left join score sc3 on s.s_id = sc3.s_id and sc3.c_id = '03'
# 第二步 筛选课程有空值的学生
where sc1.score is null or sc2.score is null or sc3.score is null;

第一步结果

第二步结果

老师的方法一：

(首先算出所有课程加起来应该有多少门，然后用student 表左连接score表，再然后按学生id分组，最后计算每位学生的课程门数)

set @allcount = (select count(*) from course);
select s.* ,count(c_id)
from student s left join score sc on s.s_id = sc.s_id
group by sc.s_id having count(c_id) <>@allcount;

老师方法二：

select s.* , count(c_id) 
from student s left join score sc on s.s_id = sc.s_id 
group by sc.s_id having count(c_id)<>(select count(*) from course);