表s,學生信息表
sid | sname
------------+------------
1111 | a
1112 | b
1113 | c
1115 | d
1116 | e
(5 rows)
表c,課程信息表
cid | cname
-------+------------
01 | abc
02 | abd
03 | abe
04 | abf
(4 rows)
表sc,選課信息表
sid | cid
-------+-------
1111 | 01
1111 | 02
1111 | 03
1112 | 03
1114 | 01
1114 | 03
1113 | 01
1113 | 02
1113 | 04
查詢只選了一課的學生的名字。
SELECT sname from s where sid in
( select sid from sc group by sid having count(*) = 1) ;
同樣可以查詢沒有選課的學生,只要判斷它不在已經選課的學生的集合裏。
SELECT sname from s where sid not in
( select sid from sc group by sid having count(*) >= 1) ;
同樣也可以引申到只被選了一次的課程。
SELECT cname from c where cid in
( select cid from sc group by cid having count(*) = 1) ;
查询选修了课程号为'01','02'的学生学号。
select s.sid,s.sname from s,sc where sc.sid = s.sid and sc.cid = '01' and sc.sid in (select s.sid from s,sc where sc.sid = s.sid and sc.cid ='02');
select sid from (select sid from sc where sc.cid in('01','02'))x group by sid having count(*)=2;
如果查询选修了全部课程的则:
select sid from (select sid from sc where sc.cid in('01','02'))x group by sid having count(*)= (select count(*) from c);
select Sname from student where Sno IN (select Sno from SC group by Sno having count(*) = (select count(*) from course ))
group by 的 查询项目,和从句的项目均要一致。
阅读(2066) | 评论(0) | 转发(0) |