Oracle分段查詢

標籤：

    網上查看分段查詢的例子，用的最多的是LAG和LEAD統計函數，Lag和Lead函數可以在一次查詢中取出同一欄位的前N行的資料和後N行的值。這種操作可以使用對相同表的表串連來實現，不過使用LAG和LEAD有更高的效率。例如：

create table TEST(  GRADE NUMBER not null,  STUID VARCHAR2(4));insert into test (GRADE, STUID)values (1, '1001');insert into 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');insert 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 (10, '1015');insert into test (GRADE, STUID)values (11, '1017');insert into test (GRADE, STUID)values (12, '1018');insert into test (GRADE, STUID)values (13, '1020');insert into test (GRADE, STUID)values (14, '1021');insert into test (GRADE, STUID)values (21, '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起始值，對k列排序，取K2列中下一位是那個數字          from (select *                  from (select id1, id2,  id2 - id1,                               (case when id2 - id1 = 1 then 1 else id1 end) k,   --如果不連續顯示開始ID                               (case when id2 - id1 = 1 then id1 else id2 end) k2 --如果不連續顯示結束ID                          from (select to_number(stuid) id1,                                       lead(to_number(stuid), 1, (select min(stuid) from test)) over(order by stuid) as id2 --1001起始值，擷取id1下個id                                  from test))                 where k > 1 --只取不連續數字              )      ) g 

Oracle分段查詢