17,377
社区成员
发帖
与我相关
我的任务
分享
with tmp(id, class , grade)as
(select '1','a','23' from dual union all
select '2','b','33' from dual union all
select '3','c','43' from dual union all
select '4','a','53' from dual union all
select '6','b','55' from dual union all
select '7','c','33' from dual)
select subtmp.xuhao,t.*
from (select class,row_number() over(order by class) xuhao
from tmp group by class) subtmp,
tmp t
where t.class=subtmp.class
order by t.class,t.grade
with tmp(id, 班级 , 成绩)as
(select '1','a','23' from dual union all
select '2','b','33' from dual union all
select '3','c','43' from dual union all
select '4','a','53' from dual union all
select '6','b','55' from dual union all
select '7','c','33' from dual)
select sum(r)over(order by rownum) as 序号,id,班级,成绩 from
(select t.*,
case
when lag(班级) over(partition by 班级 order by 成绩) is null then
1
else
0
end r
from tmp t)
第二种:
with tmp(id, 班级 , 成绩)as
(select '1','a','23' from dual union all
select '2','b','33' from dual union all
select '3','c','43' from dual union all
select '4','a','53' from dual union all
select '6','b','55' from dual union all
select '7','c','33' from dual)
select row_number() over(partition by 班级 order by 成绩) as 序号, t.*
from tmp t