Such as select * from the table to get a set of consecutive Numbers 01,02,03,04,05,06... 25 , up to a total of 25 pieces, but not every time the query is full 25 piece, May 05, 11 pieces missing , now need to implement group showed as follows: 01-04 practices - 10, 12-25 , is the missing number automatically numerical integral before and after! Could you tell me the oracle package fuction how to write

CodePudding user response:

Tucked the SQL function to

 select * from (
The select t2. Num, sum (decode (t2) num, null, 1, 0)) over (order by t1. Tot) group_id 
The from 
(select to_char (level, 'fm09) tot the from dual connect by level & lt; T1=25), 
(select * from (
The select to_char (level, 'fm09) num from dual connect by level & lt; T2=25) where t2. Num is not in (' 05, '11)) t2 
Where a t1. Tot=t2. Num (+) 
) t1 
Where a t1. Num is not null 
The order by t1. Num 
; 

CodePudding user response:

 
- this is a more classic questions, 

SQL> 
SQL> Create table test 
2 as 
3 select rownum id from dual connect by rownum & lt;=25; 
The Table created 
SQL> Delete the test where id in (3,7,12,13,17,19,22); 
7 rows does 
SQL> Select * from the test; 
ID 
-- -- -- -- -- -- -- -- -- -- 
1 
2 
4 
5 
6 
8 
9 
10 
11. 
14 
15 
16 
18 
20 
21 
23 
24 
25 
18 rows selected 
SQL> As with m (
Select id, 2 row_number () over (order by id) rn from the test t 
3) 
4 select id - rn, min (id), Max (id) 
5 the from m 
6 group by id - rn 
7 the order by 1; 
ID - RN MIN (ID) MAX (ID) 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
0 1 2 
1 4 6 
2 August 11 
4 and 16 
May 18 18 
June 20, 21 
July 23 to 25 
7 rows selected 
SQL> Drop table test purge; 
Table dropped 

SQL>