具体如下:
--测试一
SQL> WITH t AS(
2 SELECT 1 ID, 1 c1 FROM dual UNION ALL
3 SELECT 2 ID, 1 c1 FROM dual UNION ALL
4 SELECT 3 ID, 1 c1 FROM dual UNION ALL
5 SELECT 4 ID, 1 c1 FROM dual UNION ALL
6 SELECT 5 ID, 0 c1 FROM dual UNION ALL
7 SELECT 6 ID, 0 c1 FROM dual UNION ALL
8 SELECT 7 ID, 1 c1 FROM dual UNION ALL
9 SELECT 8 ID, 0 c1 FROM dual UNION ALL
10 SELECT 9 ID, 1 c1 FROM dual UNION ALL
11 SELECT 10 ID, 1 c1 FROM dual
12 )
13 select id,
14 c1,
15 sum(c1) over(partition by rn order by id) c2
16 from
17 (select id,
18 c1,
19 max(rn) over(order by id) rn
20 from
21 (select id,
22 c1,
23 decode(c1, lag(c1) over(order by id), 0,row_number() over(order by id)) rn
24 from t))
25 ;
ID C1 C2
---------- ---------- ----------
1 1 1
2 1 2
3 1 3
4 1 4
5 0 0
6 0 0
7 1 1
8 0 0
9 1 1
10 1 2
10 rows selected
SQL>
--测试二
SQL> WITH t AS(
2 SELECT 1 ID, 0 c1 FROM dual UNION ALL
3 SELECT 2 ID, 0 c1 FROM dual UNION ALL
4 SELECT 3 ID, 1 c1 FROM dual UNION ALL
5 SELECT 4 ID, 1 c1 FROM dual UNION ALL
6 SELECT 5 ID, 1 c1 FROM dual UNION ALL
7 SELECT 6 ID, 0 c1 FROM dual UNION ALL
8 SELECT 7 ID, 1 c1 FROM dual UNION ALL
9 SELECT 8 ID, 0 c1 FROM dual UNION ALL
10 SELECT 9 ID, 1 c1 FROM dual UNION ALL
11 SELECT 10 ID, 0 c1 FROM dual
12 )
13 select id,
14 c1,
15 sum(c1) over(partition by rn order by id) c2
16 from
17 (select id,
18 c1,
19 max(rn) over(order by id) rn
20 from
21 (select id,
22 c1,
23 decode(c1, lag(c1) over(order by id), 0,row_number() over(order by id)) rn
24 from t))
25 ;
ID C1 C2
---------- ---------- ----------
1 0 0
2 0 0
3 1 1
4 1 2
5 1 3
6 0 0
7 1 1
8 0 0
9 1 1
10 0 0
10 rows selected