Oracle Segmented queries

The most common example of a segmented query is the lag and lead statistics functions, which allow the lag and lead functions to fetch the first n rows of the same field and the values of the last n rows in a single query. This can be done using table joins to the same table, but using lag and lead is more efficient. For example:

The CREATE TABLE TEST (GRADE number not NULL, Stuid VARCHAR2 (4)); INSERT into TEST (GRADE, Stuid) VALUES (1, ' 1001 '); To test (GRADE, Stuid) VALUES (2, ' 1002 '); INSERT into Test (GRADE, Stuid) VALUES (3, ' 1003 '); INSERT into Test (GRADE, Stuid) VALUES (4, ' 1005 '); INSERT into Test (GRADE, Stuid) VALUES (5, ' 1006 '); INSERT into Test (GRADE, Stuid) VALUES (6, ' 1008 '); ins ert into Test (GRADE, Stuid) VALUES (7, ' 1010 '); INSERT into Test (GRADE, Stuid) VALUES (8, ' 1011 '); INSERT into Test (GRADE, STUID) VALUES (9, ' 1012 '); INSERT into Test (GRADE, Stuid) VALUES ("1015"); INSERT into Test (GRADE, Stuid) VALUES (11, ' 10 INSERT into Test (GRADE, Stuid) VALUES ("1018"), insert into Test (GRADE, Stuid) VALUES ("1020"); INSERT into TES T (GRADE, Stuid) VALUES (+, ' 1021 '), insert into Test (GRADE, Stuid) VALUES ("1022"), Commit;select (case when K-KK ; 0 Then KK | | '~' || K Else K | | "End) JG from (select K K, K2 K2, Lag (K2, 1, (select min (stuid) from test)" over "(order by K) as KK--1001 start value, sort K column, take K2 column The next one is that number from (SELECT * FROM (select Id1, Id2, ID2-ID1, ID2-ID1 = 1 Then 1 else id1 end) K,--if the start ID is not displayed consecutively (case whe                                       n id2-id1 = 1 then id1 else Id2 end) K2--If the end ID from (select To_number (stuid) ID1 is not displayed consecutively, Lead (To_number (Stuid), 1, (select min (stuid) from Test)-Over (order by Stuid) as Id2--100  1 starting value, get id1 next ID from test)) where K > 1--only take discontinuous digits))  G

Oracle Segmented queries