Oracle segmented Query
For example, you can use the LAG and LEAD statistical functions to query segments in Oracle on the Internet, the Lag and Lead functions can retrieve the data of the first N rows and the value of the last N rows of the same field in a query. This operation can be achieved through table join for the same table, but LAG and LEAD are more efficient. For example:
Create table TEST
(
Grade number not null,
STUID VARCHAR2 (4)
);
Insert into test (GRADE, STUID) values (1, '20140901 ');
Insert into test (GRADE, STUID) values (2, '20140901 ');
Insert into test (GRADE, STUID) values (3, '2013 ');
Insert into test (GRADE, STUID) values (4, '20140901 ');
Insert into test (GRADE, STUID) values (5, '20140901 ');
Insert into test (GRADE, STUID) values (6, '20140901 ');
Insert into test (GRADE, STUID) values (7, '123 ');
Insert into test (GRADE, STUID) values (8, '20140901 ');
Insert into test (GRADE, STUID) values (9, '20140901 ');
Insert into test (GRADE, STUID) values (10, '123 ');
Insert into test (GRADE, STUID) values (11, '20140901 ');
Insert into test (GRADE, STUID) values (12, '201312 ');
Insert into test (GRADE, STUID) values (13, '20140901 ');
Insert into test (GRADE, STUID) values (14, '20140901 ');
Insert into test (GRADE, STUID) values (21, '20140901 ');
Commit;
Select (case when k-kk> 0 then kk | '~ '| K else k | ''end) jg
From (select k, k2 k2,
Lag (k2, 1, (select min (stuid) from test) over (order by k) as kk -- 1001 start value, sorts k columns, the number in the lower part of column K2.
From (select *
From (select id1, id2, id2-id1,
(Case when id2-id1 = 1 then 1 else id1 end) k, -- if the start ID is not consecutive
(Case when id2-id1 = 1 then id1 else id2 end) k2 -- if the end ID is not continuous
From (select to_number (stuid) id1,
Lead (to_number (stuid), 1, (select min (stuid) from test) over (order by stuid) as id2 -- 1001 start value, get the next id of id1
From test ))
Where k> 1 -- only take discontinuous numbers
)
) G