[½»Á÷] ÇóÌÖÂÛ£¬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 sudo at 2011-06-16 11:00:47:
¿´À´ÕâÀïµÄÈ˶¼²»Ñо¿SQL°¡£¬ÎÒÒ²Ò»Çϲ»Í¨~

Originally posted by xiangling1 at 2011-06-15 19:25:01:
¸ã½ðÈÚÔõô¸ÉÉϳÌÐòÁË