如何sql查询出连续号码段 (单号连续性检查)

来源：互联网发布：linux进入文件的目录编辑：程序博客网时间：2024/05/16 01:12

有一个表结构，

SQL> select * from t;      FPHM       KSHM---------- ----------      2014          1      2014          2      2014          3      2014          4      2014          5      2014          7      2014          8      2014          9      2013        120      2013        121      2013        122      2013        124      2013        12513 rows selected.

(第二个字段内可能是连续的数据，可能存在断点。)
怎样能查询出来这样的结果,查询出连续的记录来。
就像下面的这样？
2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122

2013,00000124,00000125

最佳实践：

SQL> select b.fphm,min(b.kshm),max(b.kshm)    from (            select a.*,to_number(a.kshm-rownum) cc            from (                    select * from t order by fphm,kshm            ) a    )  b    group by b.fphm,b.cc    /
结果：1~5连续

     7~9连续

     120~122连续

     124~125连续

      FPHM MIN(B.KSHM) MAX(B.KSHM)---------- ----------- -----------      2013         120         122      2013         124         125      2014           1           5      2014           7           9

方法一: 引用自hmxxyy.

SQL> select * from gap;

ID SEQ
---------- ----------
1 1
1 4
1 5
1 8
2 1
2 2
2 9

select res1.id, res2.seq str, res1.seq end
from (
select rownum rn, c.*
from (
select *
from gap a
where not exists (
select null from gap b where b.id = a.id and a.seq = b.seq - 1
)
order by id, seq
) c
) res1, (
select rownum rn, d.*
from (
select *
from gap a
where not exists (
select null from gap b where b.id = a.id and a.seq = b.seq + 1
)
order by id, seq
) d
) res2
where res1.id = res2.id
and res1.rn = res2.rn
/

ID STR END
--------- ---------- ----------
1 1 1
1 4 5
1 8 8
2 1 2
2 9 9

方法二: 使用lag/lead分析函数进行处理.. 楼上的方法确实挺好用就是觉得表扫描/表连接比较多, 可能数据量大了. 速度会比较慢, 当然我的这种方法由于使用分析函数使用的比较频繁.所以排序量可能比上一种要多..

SQL> select fphm,lpad(kshm,8,'0') kshm
  2  from t
  3  /

      FPHM KSHM                                                                 
---------- ----------------                                                     
      2014 00000001                                                             
      2014 00000002                                                             
      2014 00000003                                                             
      2014 00000004                                                             
      2014 00000005                                                             
      2014 00000007                                                             
      2014 00000008                                                             
      2014 00000009                                                             
      2013 00000120                                                             
      2013 00000121                                                             
      2013 00000122                                                             

      FPHM KSHM                                                                 
---------- ----------------                                                     
      2013 00000124                                                             
      2013 00000125                                                             

13 rows selected.

SQL> set echo on
SQL> @bbb.sql
SQL> select fphm,lpad(kshm,8,'0') start_kshm,lpad(prev_prev_kshm,8,'0') end_kshm
  2  from (
  3    select fphm,kshm,next_kshm,prev_kshm,
  4   lag(kshm,1,null) over (partition by fphm order by kshm )next_next_kshm,
  5   lead(kshm,1,null) over (partition by fphm order by kshm ) prev_prev_kshm
  6    from (
  7   select *
  8   from (
  9     select fphm,kshm,
 10       lead(kshm,1,null) over (partition by fphm order by kshm) next_kshm,
 11       lag(kshm,1,null) over (partition by fphm order by kshm) prev_kshm
 12     from t
 13   )
 14   where ( next_kshm - kshm <> 1 or kshm - prev_kshm <> 1 )
 15   or ( next_kshm is null or prev_kshm is null )
 16    )
 17  )
 18  where next_kshm - kshm = 1
 19  /

      FPHM START_KSHM       END_KSHM                                            
---------- ---------------- ----------------                                    
      2013 00000120         00000122                                            
      2013 00000124         00000125                                            
      2014 00000001         00000005                                            
      2014 00000007         00000009                                            

SQL> spool off

0 0