比如select * from table 得到一組連續數字01,02,03,04,05,06...25，最多一共25個片號，但是不是每次查詢都是滿25片，有可能05號、11號片缺失，現在需要實作分組顯示如下：01-04,06-10,12-25，就是缺失的數字能自動前后數值分割！請問oracle的package fuction怎么寫
uj5u.com熱心網友回復：
把這個sql塞進function中去select * from (
select t2.num, sum(decode(t2.num, null, 1, 0)) over(order by t1.tot) group_id
from
(select to_char(level, 'fm09') tot from dual connect by level <= 25) t1,
(select * from (
select to_char(level, 'fm09') num from dual connect by level <= 25) t2 where t2.num not in ('05', '11')) t2
where t1.tot = t2.num(+)
) t1
where t1.num is not null
order by t1.num
;
uj5u.com熱心網友回復：
-- 這是一個比較經典的問題了。
SQL>
SQL> create table test
2 as
3 select rownum id from dual connect by rownum <= 25;
Table created
SQL> delete test where id in(3,7,12,13,17,19,22);
7 rows deleted
SQL> select * from test;
ID
----------
1
2
4
5
6
8
9
10
11
14
15
16
18
20
21
23
24
25
18 rows selected
SQL> with m as (
2 select id, row_number() over(order by id) rn from test t
3 )
4 select id - rn, min(id), max(id)
5 from m
6 group by id - rn
7 order by 1;
ID-RN MIN(ID) MAX(ID)
---------- ---------- ----------
0 1 2
1 4 6
2 8 11
4 14 16
5 18 18
6 20 21
7 23 25
7 rows selected
SQL> drop table test purge;
Table dropped
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/48153.html
標籤：開發
上一篇：cheak陳述句哪兒錯了？
下一篇：Hello world