请高手帮忙解答以下问题,非常感谢!有一个列c1,它的各行值如下,希望能通过一条sql,加上一列c2,实现对连续的非0行进行分组编号,效果如下:sort_id c1 c2
1 0 0
2 16 1
3 22 1
4 20 1
5 0 0
6 0 0
7 18 2
8 19 2
9 29 2
10 0 0
11 0 0
12 17 3
13 0 0
14 18 4
15 45 4
16 0 0
1 0 0
2 16 1
3 22 1
4 20 1
5 0 0
6 0 0
7 18 2
8 19 2
9 29 2
10 0 0
11 0 0
12 17 3
13 0 0
14 18 4
15 45 4
16 0 0
想不出来怎么通过一条sql得到 c2 列,请高手帮帮忙,多谢!
c1 c2
0 0
16 1
22 1
20 1
0 0
0 0
18 2
19 2
29 2
0 0
0 0
17 3
0 0
18 4
45 4
0 0
即使仅使用SQL可实现,效率也不会太好。
SQL> WITH t AS (
2 SELECT 1 sort_id, 0 c1 FROM DUAL UNION ALL
3 SELECT 2 sort_id, 16 c1 FROM DUAL UNION ALL
4 SELECT 3 sort_id, 22 c1 FROM DUAL UNION ALL
5 SELECT 4 sort_id, 20 c1 FROM DUAL UNION ALL
6 SELECT 5 sort_id, 0 c1 FROM DUAL UNION ALL
7 SELECT 6 sort_id, 0 c1 FROM DUAL UNION ALL
8 SELECT 7 sort_id, 18 c1 FROM DUAL UNION ALL
9 SELECT 8 sort_id, 19 c1 FROM DUAL UNION ALL
10 SELECT 9 sort_id, 29 c1 FROM DUAL UNION ALL
11 SELECT 10 sort_id, 0 c1 FROM DUAL
12 )
13 SELECT n.sort_id,
14 n.c1,
15 n.c2
16 FROM (SELECT m.sort_id,
17 m.c1,
18 DENSE_RANK() OVER(ORDER BY rn) c2
19 FROM (SELECT t.sort_id,
20 t.c1,
21 t.sort_id - ROWNUM rn
22 FROM t
23 WHERE t.c1 > 0) m
24 UNION ALL
25 SELECT sort_id,
26 c1,
27 0
28 FROM t
29 WHERE c1 = 0) n
30 ORDER BY n.sort_id
31 ; SORT_ID C1 C2
---------- ---------- ----------
1 0 0
2 16 1
3 22 1
4 20 1
5 0 0
6 0 0
7 18 2
8 19 2
9 29 2
10 0 010 rows selected