如何sql查询出连续号码段

reborn · 发表于 2005-4-26 16:16

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

我有一个表结构，
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

我菜故我在 · 发表于 2005-4-26 17:04

只用一个sql语句我也写不出来这个结果,如果写个过程还行

期待高手的答案

hmxxyy · 发表于 2005-4-26 19:28

这个有意思，容我仔细想一想。

hmxxyy · 发表于 2005-4-26 19:55

抛一块砖

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

jametong · 发表于 2005-4-26 22:35

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

复制代码

longwansheng · 发表于 2005-4-27 11:10

UP,
又學了一點.

reborn · 发表于 2005-4-28 15:47

多谢hmxxyy和jametong，问题解决

hmxxyy · 发表于 2005-4-30 00:54

还真引出玉来了

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  /

引自http://blog.itpub.net/post/5042/27936

绝！

呆呆木 · 发表于 2005-4-30 08:17

学习，谢谢！

shiguibao · 发表于 2005-4-30 10:12

收了

[精华] 如何sql查询出连续号码段

浏览过的版块