怎么用Oracle解析函数快速检查序列间隙
发布网友
共1个回答
热心网友
使用分析数据，通过巧用窗口函数，来计算前后会值差，解决此问题；
例，现在有表XXX,其数据如下：
-- Create table
create table XXX
(
id NUMBER
);
delete xxx;
insert into xxx(id) values(20);
insert into xxx(id) values(30);
insert into xxx(id) values(32);
insert into xxx(id) values(91);
insert into xxx(id) values(137);
insert into xxx(id) values(162);
insert into xxx(id) values(184);
insert into xxx(id) values(198);
insert into xxx(id) values(388);
insert into xxx(id) values(422);
insert into xxx(id) values(496);
insert into xxx(id) values(598);
insert into xxx(id) values(627);
insert into xxx(id) values(673);
insert into xxx(id) values(739);
insert into xxx(id) values(824);
insert into xxx(id) values(827);
insert into xxx(id) values(848);
insert into xxx(id) values(912);
insert into xxx(id) values(942);
commit;
然后用如下语句查出间隔，判断dx>1即可
with t as
(select a.*,
first_value(a.id) over(order by a.id rows between 1 preceding and current row) id0
from xxx a)
select t.*, t.id - t.id0 dx from t;
