MySQL怎么查询课程信息_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);