oracle中分段查询,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

原文：http://blog.csdn.net/mh942408056/article/details/43603807