在网上发现的一个经典问题,有标准的答案,分享下自己当时的思路。
问题:查询选修全部课程的学生姓名。
学生信息表S:students(sno,sname,sex,age,sdept)
课程信息表C:courses(cno,cname,cdept,tname)
选课成绩表G:grades(sno,cno,score)
我的思路:(如果有错误的地方,希望有人能提出来)
1.通过grades表得出学号和对应选修课的数量。
select sno,count(cno)as cnum from grades group by sno
2. 通过courses表得出选修课的数量。
with cte as (select count(*) as c from courses group by cno) select count(*) as cnum from cte
3.查找选修全部课程的学号。
select sno from 结果1 where 结果1的cnum=结果2的cnum
4.查找选修全部课程的学生姓名。
连接结果3和students表即可得到。
我的答案:
with cte1 as (select sno,count(cno)as cnum from grades group by sno),
with cte2 as (select count(*) as c from courses group by cno)
select sname from students
join cte1 on studenfts.sno=cte1.sno where cte1.sno in(
select sno from cte1 where cnum=(select count(*) as cnum from cte2)
)
标准答案:
select sname
from students
where not exists (
select *
from courses
where not exists(
select *
from grades
where sno=students.sno and cno=courses.cno
)
)
标准答案分析:1.查找学生所选的所有课程及成绩
select *from grades where sno=students.sno and cno=courses.cno
2.查找学生没有选修的课程
select * from courses where not exists(select...)
3.查找选修全部课程的学生姓名
select sname from students where not exists (select * from courses where not exists(select...))