[交流] 求讨论，SQL对子表的检索方法 已有2人参与

create table S(
        sno char(4) primary key,
        sname varchar(16)
);

create table C(
        cno char(4) primary key,
        cname varchar(16)
);

create table SC(
        sno char(4) not null,
        cno char(4) not null,
        grade numeric(4, 1) default 0,
        primary key(sno, lno),
        foreign key(sno) references S(sno),
        foreign key(cno) references C(cno)
);

select S.sno as 学号, S.sname as 姓名 from S
where S.sno in
(select SC1.sno from
(select SC.sno from SC where SC.cno = 'C1') as SC1,
(select SC.sno from SC where SC.cno <> 'C2') as SC2
where SC1.sno = SC2.sno);

select S.sno as 学号, S.sname as 姓名
from S where S.sno in(
select SC.sno from SC group by SC.sno
having count(SC.cno) = (select COUNT(*) from C));

select S.sno as 学号, S.sname as 姓名 from
S, (select SC.sno from SC
where SC.sno = 'S2') as SC2 left join SC
on SC.sno=SC2.sno
where S.sno = SC.sno
group by S.sno,S.sname
having COUNT(SC.cno) =
(select COUNT(SC.cno) from SC where SC.sno = 'S2');

Originally posted by xiangling1 at 2011-06-15 19:25:01:
搞金融怎么干上程序了

Originally posted by sudo at 2011-06-16 11:00:47:
看来这里的人都不研究SQL啊，我也一窍不通~