select name from (
select a.sname as name from student a , course b
where a.sno = b.sno(+) group by a.sname order by count(*) desc )
where rownum < 3
没有测试!
select a.sname as name from student a , course b
where a.sno = b.sno(+) group by a.sname order by count(*) desc )
where rownum < 3
没有测试!
select sname from (select a.sno,sname,count(1) con from course b,student a where a.sno=b.sno group by a.sno,a.sname) order by con desc where rownum<3
就是這樣的
from Student a,(select sno,count(cno) cno
from course
group by sno,cno
order by count(cno) desc )b
where a.sno=b.sno)
where rownum<3;
Therefore, perhaps can try more standard SQL statement solution:
select x.SName,x.tot
from
(select a.SNo,count(a.CNo) as tot,max(b.SName) as SName from Course a,Student b group by a.SNo) x
where
(
select count(y.SNo)
from
(select a.SNo,count(a.CNo) as tot from Course a group by a.SNo) y
where y.tot>x.tot
) < 2
[No done test!!! Please verify it]
from course c,student s where s.sno=c.sno group by sname order by cno_t desc)
where rownum<3
create table student(
sno int not null primary key,
sname varchar(10)); create table course(
sno int not null,
cno varchar(10) not null,
grade tinyint); select a.sno,a.sname, count(b.sno) as count
from student a, course b
where a.sno= b.sno
group by a.sno
order by count desc
limit 2;
我猜测题意并不允许采用rownum和limit这样的关键字。
请参考以前出现过的“选出前n名”这样的题目。只能采用SELECT,FROM,WHERE,MAX/MIN/...等传统的关键字。
where sno in
(select sno from (select sno,count(sno) cnt from course group by sno) a
where (select count(*) from (select sno,count(sno) cnt from course group by sno) b where cnt>=a.cnt)<3)
测试:create table student(sno int,sname varchar(10))
create table course(sno int,cno int,grade decimal(4,2))
insert into student select 1,'张三'
insert into student select 2,'王五'
insert into student select 3,'李四'
insert into student select 4,'马六'
insert into course select 1,10,90.2
insert into course select 1,20,80.2
insert into course select 1,30,87.2
insert into course select 2,10,70.2
insert into course select 2,20,60.2
insert into course select 3,10,77.2
insert into course select 3,20,87.2
insert into course select 3,30,97.2
insert into course select 4,10,77.2
insert into course select 4,20,97.2结果:
sname
----------
张三
李四(2 row(s) affected)