组内排序和组外排序的sql写法

表
id       班级        成绩
1          a              23
2          b              33
3          c               43
4          a               53
6          b               55
7          c               33

请教1：组外排序，以组为单位排序，并且给序号，以班级为分组
排成
序号     id       班级        成绩
   1        1           a             23
   1        4           a             53
   2        2           b             33
   2        7           b             55
   3        3           c             43
   3        6           c             33

请教2：组内排序
排成
序号     id       班级        成绩
   1        1           a             23
   2        4           a             53
   1        2           b             33
   2        7           b             55
   1        6           c             33
   2        3           c             43

oracle :
第一种（组外排序）:

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)

具体实例（ps：实际项目中的）

或者使用lag函数

lead () 下一个值 

lag() 上一个值

如果lag(t.id) 根据id分组，如果上一条数据id是不相同（空的）（不存在的），那么标号数字位1，否则为0，最后合计为序号。

select sum(r) over(order by rownum) num, f.* from (
         SELECT t.*,
            case
            when lag(t.id) over(partition by t.ID order by t.LDATE) is null then
             1
            else
             0
            end r
            FROM vb_orders t,vb_orders_chr oc
            where t.id = oc.order_id
            )  f

第二种（组内排序）:

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

实例：

select row_number() over(partition by c.id order by c.ldate) as 序号, c.*
  from (SELECT t.*
            FROM vb_orders t,vb_orders_chr oc
            where t.id = oc.order_id) c