mysql 查询多个号段_SQL查询连续号码段的巧妙解法

昨天在itpub看到这个帖子, 问题觉得有意思,, 就仔细想了想. 也给出了一种解决办法..:-)

问题求助,请高手指点..

我有一个表结构，

fphm,kshm

2014,00000001

2014,00000002

2014,00000003

2014,00000004

2014,00000005

2014,00000007

2014,00000008

2014,00000009

2013,00000120

2013,00000121

2013,00000122

2013,00000124

2013,00000125

(第二个字段内可能是连续的数据，可能存在断点。)

怎样能查询出来这样的结果,查询出连续的记录来。

就像下面的这样？

2014,00000001,00000005

2014,00000009,00000007

2013,00000120,00000122

2013,00000124,00000125

方法一: 引用自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

方法三: 今天早上wildflower给了我这个答案, 顿时觉得耳目一新啊..就贴出来与大家一起共享了^_^.

SQL> spool aaa.log

SQL> set echo on

SQL> select * from t;

no rows selected

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

FPHM       KSHM

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

2013        124

2013        125

13 rows selected.

SQL> @bbb.sql

SQL> select b.fphm,min(b.kshm),max(b.kshm)

2  from (

3          select a.*,to_number(a.kshm-rownum) cc

4          from (

5                  select * from t order by fphm,kshm

6          ) a

7  )  b

8  group by b.fphm,b.cc

9  /

FPHM MIN(B.KSHM) MAX(B.KSHM)

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

2013         120         122

2013         124         125

2014           1           5

2014           7           9

SQL>