快捷導(dǎo)航

SQL查詢連續(xù)號(hào)碼段的巧妙解法

更新時(shí)間：2013年09月25日 15:16:38 作者：

在ITPUB上有一則非常巧妙的SQL技巧，學(xué)習(xí)一下，記錄在這里

昨天在itpub看到這個(gè)帖子, 問(wèn)題覺(jué)得有意思,, 就仔細(xì)想了想. 也給出了一種解決辦法..:-)

問(wèn)題求助,請(qǐng)高手指點(diǎn)..

我有一個(gè)表結(jié)構(gòu)，
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

(第二個(gè)字段內(nèi)可能是連續(xù)的數(shù)據(jù)，可能存在斷點(diǎn)。)

怎樣能查詢出來(lái)這樣的結(jié)果,查詢出連續(xù)的記錄來(lái)。

就像下面的這樣？

2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125

方法一: 引用自hmxxyy.

復(fù)制代碼代碼如下:

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分析函數(shù)進(jìn)行處理.. 樓上的方法確實(shí)挺好用就是覺(jué)得表掃描/表連接比較多, 可能數(shù)據(jù)量大了. 速度會(huì)比較慢, 當(dāng)然我的這種方法由于使用分析函數(shù)使用的比較頻繁.所以排序量可能比上一種要多..

復(fù)制代碼代碼如下:

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給了我這個(gè)答案, 頓時(shí)覺(jué)得耳目一新啊..就貼出來(lái)與大家一起共享了^_^.

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>