需求
我有这样一个要求:
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就是下一个范围的起点