如何sql查询出连续号码段

yaoronghui2005

cj402444206 发表于 2010-8-10 16:46
SELECT FPHM,MIN(KSHM),MAX(KSHM) FROM T HAVING MAX(KSHM)-MIN(KSHM)>=1 GROUP BY FPHM,KSHM-ROWNUM;或者S ...

* 基本思想就是：一组数减去一组连续的数,如果得到的值是定值,则被减数也是一组连续的数。

solomon_007

--20200107 今天研究了一下，利用树型结构的connect by 找到一种新的解法：
SQL> with t(fphm,kshm) as (
  2  select 2014,00000001 from dual union all
  3  select 2014,00000002 from dual union all
  4  select 2014,00000003 from dual union all
  5  select 2014,00000004 from dual union all
  6  select 2014,00000005 from dual union all
  7  select 2014,00000007 from dual union all
  8  select 2014,00000008 from dual union all
  9  select 2014,00000009 from dual union all
10  select 2013,00000120 from dual union all
11  select 2013,00000121 from dual union all
12  select 2013,00000122 from dual union all
13  select 2013,00000124 from dual union all
14  select 2013,00000125 from dual )
15  select fphm,root,leaf
16   from (
17  select fphm,
18         connect_by_root(kshm) root,
19         kshm leaf,
20         kshm-connect_by_root(kshm) diff,
21         max(kshm - connect_by_root(kshm)) over(partition by fphm,kshm ) max_diff  --max deepth in the tree
22    from t
23   where connect_by_isleaf = 1
24  connect by nocycle prior fphm = fphm and prior kshm = kshm - 1
25  )
26  where diff = max_diff
27  /

      FPHM       ROOT       LEAF
---------- ---------- ----------
      2013        120        122
      2013        124        125
      2014          1          5
      2014          7          9         

lisan2000

mark标记一下.

sql_tigerliu

12c的时代, 用12c的match_recognize语法也可以实现:with t(fphm,kshm) as (
select 2014,00000001 from dual union all
select 2014,00000002 from dual union all
select 2014,00000003 from dual union all
select 2014,00000004 from dual union all
select 2014,00000005 from dual union all
select 2014,00000007 from dual union all
select 2014,00000008 from dual union all
select 2014,00000009 from dual union all
select 2013,00000120 from dual union all
select 2013,00000121 from dual union all
select 2013,00000122 from dual union all
select 2013,00000124 from dual union all
select 2013,00000125 from dual
)
select * from t match_recognize
(partition by fphm
order by kshm
measures
     first(kshm) as begin_kshm,
     last(kshm) as end_kshm
one row per match
pattern (a b+  )
define
       b as kshm=prev(kshm)+1
);

〇〇

sql_tigerliu 发表于 2021-9-17 12:16
12c的时代, 用12c的match_recognize语法也可以实现:with t(fphm,kshm) as ( select 2014,00000001 from dua ...

知道了