--author:daiyueqiang
--date:2009-11-12
--comment:关于自身连接的例子。(查询选了课程号为2,3学生的学号)
/*学号Sno;课程号Cno;成绩Grade*/
create table SC
(Sno int,
Cno int,
Grade int,
primary key(Sno,Cno))
go
insert into SC values('95001','1','92')
insert into SC values('95002','4','66')
insert into SC values('95001','2','92')
insert into SC values('95001','3','50')
insert into SC values('95001','4','70')
insert into SC values('95001','5','85')
insert into SC values('95001','6','64')
insert into SC values('95002','6','66')
1:
select Sno from (
select * from SC where Cno in (2,3) --查询选修了2或3的学号的学生集合
) b
group by Sno
having count(*)=2 --同时选择这两门
2:
select Sno
FROM SC s1
where Cno in (2,3)
group by Sno
having count(Cno)=2