CTE的等价写法:
SQL> with d as
2 (
3 select 1 as seq,1 as status,1 as v from dual union all
4 select 2,1,2 from dual union all
5 select 3,1,3 from dual union all
6 select 4,1,4 from dual union all
7 select 5,2,5 from dual union all
8 select 6,2,7 from dual union all
9 select 7,2,9 from dual union all
10 select 8,1,11 from dual union all
11 select 9,1,12 from dual union all
12 select 10,2,13 from dual union all
13 select 11,2,14 from dual union all
14 select 12,2,15 from dual union all
15 select 13,2,16 from dual
16 ),
17 s as (select row_number() over(order by seq) as rn,seq,status,v from d),
18 t (rn,seq,status,v,g) as (select rn,
19 seq,
20 status,
21 v,
22 1 g
23 from s
24 where rn = 1
25 union all
26 select t.rn + 1,
27 s.seq,
28 s.status,
29 s.v,
30 case when s.status = t.status then t.g else t.g + 1 end
31 from t,s
32 where t.rn = s.rn -1)
33 select max(seq) seq,max(status) status,avg(v) avg_v from t group by g order by g
34 /
SEQ STATUS AVG_V
---------- ---------- ----------
4 1 2.5
7 2 7
9 1 11.5
13 2 14.5