考试题：这个SQL语句怎么写？

[inforum]

设
S(SNO，SNAME， AGE， SEX)，其属性分别表示学号、学生姓名、年龄、性别。
SC(SNO，CNO，GRADE)，其属性分别表示学号、课程号、成绩。
请教："各门功课比王小华好的有哪些同学"怎么做？

[linzhisong]

select * from s a left join sc b on a.sno=b.sno

where b.grade >(select grade from s c left join sc d on c.sno=d.sno where s.sname='小华' and d.cno=b.cno)

[firetoucher]

SELECT SNAME FROM
S,
(SELECT SNO,COUNT(*) AS C FROM SC ,
(SELECT SNO,CNO,GRADE FROM SC,S WHERE S.SNO = SC.SNO AND S.SNAME = '王小华') A
WHERE SC.CNO = A.CNO AND SC.GRADE > A.GRADE) B
WHERE S.SNO = B.SNO AND B.C = (SELECT COUNT(*) FROM SC,S WHERE S.SNO = SC.SNO AND S.SNAME = '王小华')

[inforum]

to linzhisong(無聊) ( ) :
你的代码之列出了部分功课，我要的是各门功课比王小华好

再来看看firetoucher(风焱)的代码，请稍候...

[firetoucher]

to inforum(坛中人,来捧个场！)
有结果，给我短信，不管正不正确

[inforum]

to firetoucher(风焱) :
OK! 给U短信!THX

[linzhisong]

select * from c where
(select count(*) from s a left join sc b on a.sno=b.sno
where b.grade >(select grade from s c left join sc d on c.sno=d.sno where s.sname='小华' and d.cno=b.cno and a.s.sno=c.sno))=(SELECT COUNT(*) FROM SC,S WHERE S.SNO = SC.SNO AND S.SNAME = '王小华')

[inforum]

结果有了，谢谢2位！
综合了2位，最后答案如下：
select b.sno, count(b.CNO) from S a left join SC b on a.sno=b.sno
where b.grade >
(select grade
from S c left join SC d on c.sno=d.sno
where c.sname='王小华' and d.cno=b.cno )
group by b.sno
HAVING count(b.CNO)>= ( SELECT COUNT(*) FROM SC,S WHERE S.SNO = SC.SNO AND S.SNAME = '王小华' )

[inforum]

不用left join 也可：
select b.sno, count(b.CNO) from S a , SC b
where a.sno=b.sno and b.grade >
(select grade
from S c , SC d
where c.sno=d.sno and c.sname='王小华' and d.cno=b.cno )
group by b.sno
HAVING count(b.CNO)>= ( SELECT COUNT(*) FROM SC,S WHERE S.SNO = SC.SNO AND S.SNAME = '王小华' )

现在结帖！

[inforum]

要求学生姓名这样做：
select sname from S where sno in
(select b.sno from S a , SC b
where a.SNO=b.SNO and a.sno=b.sno and b.grade >
(select grade
from S c , SC d
where c.sno=d.sno and c.sname='王小华' and d.cno=b.cno )
group by b.sno
HAVING count(b.CNO)>= ( SELECT COUNT(*) FROM SC,S WHERE S.SNO = SC.SNO AND S.SNAME = '王小华' )
)