一條SQL語句面試題：求選修所有課程的學生，sql語句

一條SQL語句面試題：求選修所有課程的學生，sql語句

前幾天求職面試，有一道SQL題：給出三個表：學生、課程、成績，求選修了所有課程的學生。

一道看似很簡單的問題，把我難住了，我改了又改，塗塗畫畫，抓耳撓腮，因為試卷沒有多少空白位置了，最後只好放棄。心情大受影響，儘管最後還是獲得offer。

但是心中有愧呀！

於是在機器上試了試：

先建好表

use test;gocreate table student(sno varchar(50) not null,name varchar(50) not null);insert into student(sno,name) values('001','張三');insert into student(sno,name) values('002','李四');insert into student(sno,name) values('003','王五');create table class(cno varchar(50) not null,name varchar(50) not null) insert into class(cno,name) values('c01','資料結構');insert into class(cno,name) values('c02','作業系統');insert into class(cno,name) values('c03','電腦群組成原理');insert into class(cno,name) values('c04','網路基礎');create table score(sno varchar(50) not null,cno varchar(50) not null,score decimal(18,2) not null) insert into score(sno,cno,score) values('001','c01',80);insert into score(sno,cno,score) values('001','c02',85);insert into score(sno,cno,score) values('001','c03',89);insert into score(sno,cno,score) values('001','c04',87);insert into score(sno,cno,score) values('002','c01',80);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)是否要執行很多次。