一条SQL语句面试题：求选修所有课程的学生

注： 转自 http://blog.csdn.net/leftfist/article/details/44206081  仅供学习

前几天求职面试，有一道SQL题：给出三个表：学生、课程、成绩，求选修了所有课程的学生。

一道看似很简单的问题，把我难住了，我改了又改，涂涂画画，抓耳挠腮，因为试卷没有多少空白位置了，最后只好放弃。心情大受影响，尽管最后还是获得offer。

但是心中有愧呀！

于是在机器上试了试：

先建好表

[sql] view plaincopy

1. use test;  
2. go  
3.   
4. create table student(sno varchar(50) not null,name varchar(50) not null);  
5. insert into student(sno,name) values('001','张三');  
6. insert into student(sno,name) values('002','李四');  
7. insert into student(sno,name) values('003','王五');  
8.   
9. create table class(cno varchar(50) not null,name varchar(50) not null)   
10. insert into class(cno,name) values('c01','数据结构');  
11. insert into class(cno,name) values('c02','操作系统');  
12. insert into class(cno,name) values('c03','计算机组成原理');  
13. insert into class(cno,name) values('c04','网络基础');  
14.   
15. create table score(sno varchar(50) not null,cno varchar(50) not null,score decimal(18,2) not null)   
16. insert into score(sno,cno,score) values('001','c01',80);  
17. insert into score(sno,cno,score) values('001','c02',85);  
18. insert into score(sno,cno,score) values('001','c03',89);  
19. insert into score(sno,cno,score) values('001','c04',87);  
20. insert into score(sno,cno,score) values('002','c01',80);  
21. insert into score(sno,cno,score) values('003','c04',70);  

我想到了三种写法：

1、

[sql] view plaincopy

1. select * from student s  
2.     where not exists(select 1 from class c  
3.             where not exists(select 1 from score   
4.                 where sno=s.sno and cno=c.cno));  

两个not exists。我当时是只写了一个。

由内嵌到外部，

1）不存在一门为当前学生所选修的课程

select 1 from class c where not exists(select 1 from score where sno=s.sno and cno=c.cno)

2）不存在 情况1），也就是不存在当前学生有没选修的课程这种情况

换言之，当前学生选修了所有的课程

2、

[sql] view plaincopy

1. select * from student where sno not in(   
2.     select st.sno from student st,class c  
3.         where not exists(select 1 from score   
4.             where sno=st.sno and cno=c.cno)  
5. )  

嵌套里面的语句是有未选修课程的学生

然后外部是不在此名单内的学生

3、

[sql] view plaincopy

1. select * from student where sno in(  
2.     select st.sno from student st  
3.         inner join score sc on st.sno=sc.sno  
4.         group by st.sno  
5.         having count(*)=(select count(*) from class)  
6.     )  

这个语句比较容易理解。但效率可能不高，我不确定(select count(*) from class)是否要执行很多次。