connect by rn = prior cnt + prior rn

需求

我有这样一个要求：
1、查询的结果按照值排序，如sql：select value from t;
结果示例如下：
50
70
90
130
160
190
2、对数据进行分组。从上述数组第一个值开始，＋50之内的值作为同一组值，如果超出50了，则开始一个新的分组。示例如下
50    50
70    50
90    50
130  130
160  130
190  190
3、最终结果是统计每组的个数。结果示例：
50    3
130  2
190  1

nyfor

SQL> select sal from emp order by sal;
      SAL
---------
   800.00
   950.00
  1100.00
  1250.00
  1250.00
  1300.00
  1500.00
  1600.00
  2450.00
  2850.00
  2975.00
  3000.00
  3000.00
  5000.00
14 rows selected
SQL> 
SQL> select sal, cnt
  2    from (select sal,
  3                 count(0) over(order by sal range between current row and 500 following) cnt,
  4                 row_number() over(order by sal) rn
  5            from emp)
  6   start with rn = 1
  7  connect by rn = prior cnt + prior rn;
      SAL        CNT
--------- ----------
   800.00          6
  1500.00          2
  2450.00          2
  2975.00          3
  5000.00          1
SQL> .....

解法思路

SQL> select sal, cnt
      from (select sal,
                   count(0) over(order by sal range between current row and 500 following) cnt,
                   row_number() over(order by sal) rn
              from emp)
     start with rn = 1
    connect by rn = prior cnt + prior rn;
       SAL        CNT
---------- ----------
       800          6
      1500          2
      2450          2
      2975          3
      5000          2
select cast(lpad(sal,2*level-1+length(sal),' ') as varchar2(20)), cnt,rn,level
     from (select sal,
                  count(0) over(order by sal range between current row and 500 following) cnt,
                  row_number() over(order by sal) rn
             from emp)
    start with rn = 1
   connect by prior cnt + prior rn = rn;
CAST(LPAD(SAL,2*LEVE        CNT         RN      LEVEL
-------------------- ---------- ---------- ----------
 800                          6          1          1
   1500                       2          7          2
     2450                     2          9          3
       2975                   3         11          4
         5000                 2         14          5
首先，树形查询是从start with开始作为根节点，找到它的枝节点，在找枝的叶子节点
SQL> select sal,
  2        count(0) over(order by sal range between current row and 500 following) cnt,
  3        row_number() over(order by sal) rn
  4  from emp;
       SAL        CNT         RN
---------- ---------- ----------
       800          6          1
       950          5          2
      1100          6          3
      1250          5          4
      1250          5          5
      1300          3          6
      1500          2          7
      1600          1          8
      2450          2          9
      2850          4         10
      2975          3         11
      3000          2         12
      3000          2         13
      5000          2         14
      5000          2         15
15 rows selected.
CNT是这个范围的个数，那么从本行往下找 加这个个数个行数，就是下一个范围的七点
比如800  6  1
800 + 500范围内有6个，一旦超出这一范围，就是下一个范围的七点，那么用行号rn+6就是下一个范围的起点