Oracle:枚举相似行的组

这应该做到:

select id, x, sum(new_group) over (order by id) as group_no
from
( select id, x, case when x-prev_x = 1 then 0 else 1 end new_group
  from
  ( select id, x, lag(x) over (order by id) prev_x
    from mytable
  )
);

SQL> create table mytable (id,x)
  2  as
  3  select 1, 1 from dual union all
  4  select 2, 2 from dual union all
  5  select 3, 5 from dual union all
  6  select 4, 6 from dual union all
  7  select 5, 7 from dual union all
  8  select 6, 9 from dual
  9  /

Table created.

SQL> select id
  2       , x
  3       , sum(y) over (order by id) n
  4    from ( select id
  5                , x
  6                , case x - lag(x) over (order by id)
  7                  when 1 then 0
  8                  else 1
  9                  end y
 10             from mytable
 11         )
 12   order by id
 13  /

        ID          X          N
---------- ---------- ----------
         1          1          1
         2          2          1
         3          5          2
         4          6          2
         5          7          2
         6          9          3

6 rows selected.

这与Tony的答案基本相同,只是少了一个内联视图。

当做, 罗伯。

仅使用基本操作:

create table test(id int, x int);
insert into test values(1, 1), (2, 2), (3, 5), (4, 6), (5, 7), (6, 9);

create table temp as 
select rownum() r, 0 min, x max 
from test t 
where not exists(select * from test t2 where t2.x = t.x + 1);

update temp t set min = select max + 1 from temp t2 where t2.r = t.r - 1;
update temp t set min = 0 where min is null;

select * from temp order by r;

select t.id, t.x, x.r from test t, temp x where t.x between x.min and x.max;

drop table test;
drop table temp;