Oracle segmented query: Oracle segmented Query

Oracle segmented query: Oracle segmented Query

You can view the segmented query examples on the Internet. The LAG and LEAD statistical functions are the most used, 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, '123'); insert into test (GRADE, STUID) values (3, '123'); insert into test (GRADE, STUID) values (4, '123'); insert into test (GRADE, STUID) values (5, '123'); insert into test (GRADE, STUID) values (6, '123 '); insert into test (GRADE, STUID) values (7, '123'); insert into test (GRADE, STUID) values (8, '123'); insert into test (GRADE, STUID) values (9, '123 '); insert into test (GRADE, STUID) values (10, '000000'); insert into test (GRADE, STUID) values (11, '000000'); insert into test (GRADE, STUID) values (12, '20140901'); insert into test (GRADE, STUID) values (13, '20160901'); insert into test (GRADE, STUID) values (14, '123'); insert into test (GRADE, STUID) valu Es (21, '000000'); 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, and obtains the number 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 consecutive, 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 id1 next id from test) where k> 1 -- only take discontinuous numbers) g