Ò»¸öSQLÃæÊÔÌâ

 ÌâÄ¿ÒªÇó
°¢ÀïbabaµÄÃæÊÔÌâ
ÓÐÈý¸ö±í
    ѧÉú±í  S 
          SID  SNAME
  ½Ìʦ¿Î±í T 
          TID  TNAME  TCL
  ³É¼¨±í  SC   
            SID  TCL  SCR
¸÷×ֶεĺ¬Òå²»ÓÃÎÒ±êÃ÷ÁË°É£¬´óÏÀ¸ç¸çô£¡ºÇºÇ
ÏÖÔÚÒªÇóдSQL²éѯ
    1¡¢Ñ¡ÐÞÁËA¡¢B¿Î³Ì£¬²¢ÇÒA¿Î³ÌµÄ³É¼¨´óÓÚB³É¼¨µÄѧÉúÐÕÃû£¿
    2¡¢Ã»ÓÐÑ¡ÐÞ‘li’ÀÏʦµÄ¿Î³ÌµÄѧÉú£¬ÒªÇó²»ÄÜÓÃin£¬exists µÈ´Ê£¿
create table SC(
SID varchar(64) ,
TCL varchar(64) ,
SCR int) ;
create table S (
SID varchar(64) ,
SNAME varchar(64)
) ;
create table T
(
  TID varchar(64),
  TNAME varchar(64),
  TCL varchar(64)
) ;
insert into S VALUES ('1','11') ;
insert into S VALUES ('2','22') ;
insert into S VALUES ('3','33') ;
insert into S VALUES ('4','44') ;
insert into SC VALUES ('1','a',100) ;
insert into SC VALUES ('1','b',90) ;
insert into SC VALUES ('2','a',77) ;
insert into SC VALUES ('3','b',34) ;
insert into SC VALUES ('4','a',59) ;
insert into SC VALUES ('4','b',88) ;
insert into T VALUES ('5','li','a');
µÚÒ»¸öÎÊÌâµÄ´ð°¸
select
  s.sname,sc1.scr,sc2.scr
from
  s,sc sc1,sc sc2 
where s.sid=sc1.sid
    and s.sid=sc2.sid
    and sc1.tcl='a'
    and sc2.tcl='b'
    and sc1.scr>sc2.scr
   
    ÔËÐнá¹û£º
   11 £¬100 £¬ 90
   
  µÚ¶þÌâµÄ´ð°¸
 
  select sid from
(select s.sid,a.tcl from (select sc.sid,sc.tcl  from sc,t where sc.tcl=t.tcl and t.tname='li' ) a
right join s
  on a.sid=s.sid ) b
where  b.tcl is null
ÔËÐнá¹û
3