oracle号码段拆开,学习-USB迷|专注于互联网分享

oracle号码段拆开,学习

学习-SQL查询连续号码段的巧妙解法

在ITPUB上有一则非常巧妙的SQL技巧，学习一下，记录在这里。

最初的问题是这样的:我有一个表结构，

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

ITPUB上的朋友给出了一个非常巧妙的答案:SQL> SELECT b.fphm, MIN (b.kshm) Start_HM, MAX (b.kshm) End_HM

2 FROM (SELECT a.*, TO_NUMBER (a.kshm - ROWNUM) cc

3 FROM (SELECT *

4 FROM t

5 ORDER BY fphm, kshm) a) b

6 GROUP BY b.fphm, b

7 /

FPHM START_HM END_HM

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

2013 00000120 00000122

2013 00000124 00000125

2014 00000001 00000005

2014 00000007 00000009

-The End-

By eygle on 2006-09-26 15:04 |

Comments (10) |

SQL.PLSQL | 916 |

10 Comments

当数据列比较大时，行号就好像不管用了

有重复的数据就不管用了，比如有两行2014,00000005。

用到的方法很简单但是确实很使用很精妙不过严谨一点还应该给subquery进行asc排序。

--用分析函数也可以,主要是要构造出一个序列做差统一分组:

with t as

(

select'2014' fphm, '00000001' kshm from dual union all

select'2014' fphm, '00000002' kshm from dual union all

select'2014' fphm, '00000003' kshm from dual union all

select'2014' fphm, '00000004' kshm from dual union all

select'2014' fphm, '00000005' kshm from dual union all

select'2014' fphm, '00000007' kshm from dual union all

select'2014' fphm, '00000008' kshm from dual union all

select'2014' fphm, '00000009' kshm from dual union all

select'2013' fphm, '00000120' kshm from dual union all

select'2013' fphm, '00000121' kshm from dual union all

select'2013' fphm, '00000122' kshm from dual union all

select'2013' fphm, '00000124' kshm from dual union all

select'2013' fphm, '00000125' kshm from dual

)

select fphm, min(kshm), max(kshm)

from (select t.*, row_number() over(partition by fphm order by kshm) rn from t)

group by fphm,to_number(kshm-rn);