日期:2014-05-17  浏览次数:20790 次

oracle分析函数问题
请高手帮忙解答以下问题,非常感谢!

有一个列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


------解决方案--------------------
SQL code

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          0

10 rows selected