按成绩总和及年级排序: select id,name,a,b,c,grade from student order by grade,a+b+c desc找出成绩最差的10个人。如果第11人的成绩和第10人相同,是否列出?若不列出,将rank改成row_number select id,name,a,b,c from( select t.*,rank()over(partition by grade order by a+b+c)rk from student t) where rk<=10 order by grade,a+b+c
select id,name from ( select id,name,row_number() over(partition by grade,id order by grade,id desc) as rn from ( select id,name,sum(a+b+c) grade from student group by id,name ) ) where rn<11
我也写一个。一张Student表,表内有字段,id,name,a(语文),b(数学),c(英语),grade按照成绩总和和年级降序排序,并且找出成绩最差的10个人。如果只是用grade和score排序,而没有grade的分组的要求的话,直接select * from (select id, name, a, b, c, grade from studenet order by nvl(a,0)+nvl(b,0)+nvl(c,0) desc, grade desc) where rownum <=10;如果需要grade分组的话select id, name, a, b, c, grade from (select id, name, a, b, c, grade, row_number() over(partition by grade order by nvl(a,0)+nvl(b,0)+nvl(c,0) desc) r_no from studenet ) a where r_no <=10 order by grade desc
from
(select student.*,
ROW_NUMBER() OVER (PARTITION BY grade ORDER BY score) as rn
from student)
where rn<=10
一张Student表,表内有字段,id,name,a(语文),b(数学),c(英语),grade按照成绩总和和年级降序排序,并且找出成绩最差的10个人。
select id,name,a,b,c,grade
from student
order by grade,a+b+c desc找出成绩最差的10个人。如果第11人的成绩和第10人相同,是否列出?若不列出,将rank改成row_number
select id,name,a,b,c from(
select t.*,rank()over(partition by grade order by a+b+c)rk
from student t)
where rk<=10
order by grade,a+b+c
(
select id,name,row_number() over(partition by grade,id order by grade,id desc) as rn
from
(
select id,name,sum(a+b+c) grade from student
group by id,name
)
)
where rn<11