需要对以下Oracle脚本作修改,达到的目的。
取各学科考试前10名的学生。
比方有8门课,那就会有80笔记录。
ps:以下这段的意思是平均分最高的10个同学,也就是10笔记录。select t.* from
(
select subject , studentid , avg(score) score
,row_number() over (partition by subject order by avg(score) desc) as num
from tb group by subject , studentid
)t
where num <=10
取各学科考试前10名的学生。
比方有8门课,那就会有80笔记录。
ps:以下这段的意思是平均分最高的10个同学,也就是10笔记录。select t.* from
(
select subject , studentid , avg(score) score
,row_number() over (partition by subject order by avg(score) desc) as num
from tb group by subject , studentid
)t
where num <=10
(
select subject , studentid , avg(score) score
,row_number() over (partition by subject order by score desc) as num
from tb group by subject , studentid
)t
where num <=10
另外,你上面这段SQL有错误,不是平均分最高的十位,而是每门课平均分最高的10位
select t.* from
(
select subject , studentid , avg(score) score
,row_number() over ( order by avg(score) desc) as num
from tb group by subject , studentid
)t
where num <=10
要这样才是平均分最高的十位
where score in
(select score from tb where subject = t.subject and rownum <= 10 order by score desc)
select t.* from
(
select subject,studentid,avg(score) as score
from tb group by subject,studentid
order by avg(score) desc
)t
where rownum <= 10