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

代码段：

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

执行结果：

分析：

要查询没有学过所有课程的同学，即查询出所学课程数小于总课程数的学生即可。

需要注意：可能存在有些同学一门课都没有选，故在Score表中没有该学生。

所以我们需要用Student表左连接Score表，所连接的表才有全部s_id的全部c_id。

（1）查询出符合条件的s_id：

select st.s_id
	from student st left join score as sc on st.s_id=sc.s_id
	group by st.s_id
	having count(distinct sc.c_id)<(select count(c_id)from course)

（2）将（1）得到的结果作为表与学生信息表Student相连接：

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