查询一段连续的流水号之间缺失的号码

LingHwa 2017-12-02 04:16:58

一段连续的流水号，比如1~10，查询中间缺失的号码，比如，4、7、8、9。
或者更复杂一点，查询每个ID下的缺失流水号，比如ID为1，从1~10中间缺失的流水号，ID为2，从5~13的流水号。

...全文

475 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

花开了叫我 2017-12-05

打赏
举报

WITH t AS ( SELECT * FROM ( SELECT id, seq, lag(seq) over(PARTITION BY id ORDER BY seq) + 1 beg, seq - 1 en FROM tmp ） WHERE en>=beg ) SELECT a1.id, a1.beg + rn - 1 missing FROM t a1, (SELECT rownum rn FROM dual CONNECT BY rownum <= (SELECT MAX(en - beg) + 1 FROM t)) a2 WHERE a2.rn <= a1.en - a1.beg + 1 ORDER BY 1,2 --标准答案楼上那个做法效率太低

花开了叫我 2017-12-04

打赏
举报

引用 3 楼 qq646748739 的回复:

试试这个:


--1.创建测试表:
create table tmp as
select 1 id, 1 seq from dual union all
select 1 id, 2 seq from dual union all
select 1 id, 3 seq from dual union all
select 1 id, 5 seq from dual union all
select 1 id, 6 seq from dual union all
select 1 id, 10 seq from dual union all
select 2 id, 5 seq from dual union all
select 2 id, 6 seq from dual union all
select 2 id, 9 seq from dual union all
select 2 id, 10 seq from dual union all
select 2 id, 12 seq from dual union all
select 2 id, 13 seq from dual;

--2.sql实现:
select t1.id,t1.seq
  from(select distinct id,seq+b.num-1 seq from tmp a,
       (select rownum num  from tmp connect by rownum <= (select max(seq) from tmp))b)t1
   left join (select id, min(seq) min_seq, max(seq) max_seq from tmp group by id )t2 on t2.id = t1.id
  where t1.seq between t2.min_seq+1 and t2.max_seq-1
    and not exists(select null from tmp t3 where t3.id = t1.id and t3.seq = t1.seq)
  order by t1.id,t1.seq

select 1 id, 1 seq from dual union all select 1 id, 2 seq from dual union all select 1 id, 3 seq from dual union all select 1 id, 5 seq from dual union all select 1 id, 6 seq from dual union all select 1 id, 10 seq from dual union all select 2 id, 2000005 seq from dual union all select 2 id, 2000006 seq from dual union all select 2 id, 2000009 seq from dual union all select 2 id, 2000010 seq from dual union all select 2 id, 2000012 seq from dual union all select 2 id, 2000013 seq from dual; 如果你原始记录是这样的你的sql还适用么？

碧水幽幽泉 2017-12-04

打赏
举报

试试这个:



--1.创建测试表:

create table tmp as

select 1 id, 1 seq from dual union all

select 1 id, 2 seq from dual union all

select 1 id, 3 seq from dual union all

select 1 id, 5 seq from dual union all

select 1 id, 6 seq from dual union all

select 1 id, 10 seq from dual union all

select 2 id, 5 seq from dual union all

select 2 id, 6 seq from dual union all

select 2 id, 9 seq from dual union all

select 2 id, 10 seq from dual union all

select 2 id, 12 seq from dual union all

select 2 id, 13 seq from dual;



--2.sql实现:

select t1.id,t1.seq

  from(select distinct id,seq+b.num-1 seq from tmp a,

       (select rownum num  from tmp connect by rownum <= (select max(seq) from tmp))b)t1

   left join (select id, min(seq) min_seq, max(seq) max_seq from tmp group by id )t2 on t2.id = t1.id

  where t1.seq between t2.min_seq+1 and t2.max_seq-1

    and not exists(select null from tmp t3 where t3.id = t1.id and t3.seq = t1.seq)

  order by t1.id,t1.seq