sql 难句

[cjhyahoo]

表中有sno（学生号）,cno（课程号）,grade(成绩）
求：1
找出考了学号为001的学生的全部课程的学生
2，
满足1，且每门成绩都比他高的学生

[erigido]

学习

[samuelpan]

1、select * from table where cno in(select cno from table where sno='001')
and sno in(select sno from table group by sno having count(*)>
(select count(*) from table where sno='001'))

2、select a.* from table a,
(select * from table where sno='001') t
where a.cno=t.cno and a.grade>t.grade and sno in(select sno from table group by sno having count(*)>(select count(*) from table where sno='001'))
order by sno,cno

[pengdali]

1、
select * from 表 where sno in (select sno from 表 where sno<>'001' and cno in (select cno from 表 where sno='001') group by sno having sum(1)>=(select sum(1) from 表 where sno='001'))

或：

select sno from 表 where sno<>'001' and cno in (select cno from 表 where sno='001') group by sno having sum(1)>=(select sum(1) from 表 where sno='001')


2、
select * from 表 where sno in (select sno from 表 tem where sno<>'001' and exists (select 1 from 表 where sno='001' and cno=tem.cno and grade<tem.grade) group by sno having sum(1)>=(select sum(1) from 表 where sno='001'))

或：

select sno from 表 tem where sno<>'001' and exists (select 1 from 表 where sno='001' and cno=tem.cno and grade<tem.grade) group by sno having sum(1)>=(select sum(1) from 表 where sno='001')