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