提示一下,使用rank(), over, partition by (班级id), order by 学生成绩 desc, 取rank()值<=5的即可。
总成绩还是单科成绩?-- 按总成绩 with t1 as ( select id,classid,stu_name, (select sum(score) from score where studentid=s.id) score from student s ), t2 as ( select id,stu_name,classid,score, rank() over (partition by classid order by score desc) rn from t1 ) select * from t2 where rn<=5;
个人认为这是一个分页代码如下select * from (select a.* ,rowsrn rn from (select * from tablename order by 成绩)a where rowsrn<=510)b where rn>=1
select id,classid,stu_name from (select a.id,a.stu_name,a.classid,b.score, rank() over (partition by b.studentid order by b.score desc) rk from student a, score b where a.classid = b.studentid ) where rn<=5;
select * from (select a.*,b.score, dense_rank() over(partition by b.classsid order by b.score desc) rn from student a,score b where a.studentId=b.id and b.classsid=a.classsid) where rn<=5 select * from (select a.*,b.score, rank() over(partition by b.classsid order by b.score desc) rn from student a,score b where a.studentId=b.id and b.classsid=a.classsid) where rn<=5
取rank()值<=5的即可。
with t1 as (
select id,classid,stu_name,
(select sum(score) from score where studentid=s.id) score
from student s
),
t2 as (
select id,stu_name,classid,score,
rank() over (partition by classid order by score desc) rn
from t1
)
select * from t2 where rn<=5;
如果用dense_rank()产生的是1,2,2,3,4,这样的排名。
其他就如楼上们说的。
select id,classid,stu_name
from (select a.id,a.stu_name,a.classid,b.score,
rank() over (partition by b.studentid order by b.score desc) rk
from student a, score b
where a.classid = b.studentid
)
where rn<=5;
select *
from (select a.*,b.score,
dense_rank() over(partition by b.classsid order by b.score desc) rn
from student a,score b
where a.studentId=b.id and b.classsid=a.classsid)
where rn<=5
select *
from (select a.*,b.score,
rank() over(partition by b.classsid order by b.score desc) rn
from student a,score b
where a.studentId=b.id and b.classsid=a.classsid)
where rn<=5