Oracle中实现sql查询得到连续号码段

* 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 125

实现代码如下：

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

结果如下：

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

------------------------------------------------------------------------------------------------------------------------

二、表名为gap的表中数据如下：

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

Oracle中实现sql查询得到连续号码段

标签：number   ber   where   from   数据   font   连续   and   span