« 今年月又到中秋 | Blog首页 | 使用分析函数进行行列转换 »
学习-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.cc
7 /FPHM START_HM END_HM
---------- -------- --------
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005
2014 00000007 00000009
巧思妙想，就在一念之间。
ITPUB其他参考链接如下:
http://blog.itpub.net/post/5042/27936
-The End-
历史上的今天...
>> 2009-09-26文章:
>> 2008-09-26文章:
>> 2007-09-26文章:
------
这篇 【学习-SQL查询连续号码段的巧妙解法】来自 eygle.com | CSDN网摘| del.icio.us|Google订阅 | 鲜果订阅 | 抓虾订阅
By eygle on 2006-09-26 15:04 | Comments (10) | Posted to SQL.PLSQL | Edit |Pageviews:
留言 (10)
高
Posted by: leon at October 30, 2006 11:05 AM
当数据列比较大时，行号就好像不管用了
Posted by: ygjdatou at November 25, 2006 5:14 PM
当数据列比较大时，行号就好像不管用了
Posted by: ygjdatou at November 25, 2006 5:16 PM
哦，可以。不错，我自己搞错了
Posted by: ygjdatou at November 25, 2006 5:22 PM
我里面有重号，呵呵
Posted by: ygjdatou at November 25, 2006 5:30 PM
有重复的数据就不管用了，比如有两行2014,00000005。
Posted by: feelfall at January 3, 2007 7:12 PM
有重复数据，嵌套一下去除重复数据就可以了。
Posted by: eygle at January 4, 2007 10:04 AM
用到的方法很简单 但是确实很使用 很精妙 不过严谨一点还应该给subquery进行asc排序。
Posted by: Anonymous at May 7, 2009 2:04 AM
不得不承认我想不出更好的方法。
Posted by: nbby at June 5, 2009 10:01 AM
--用分析函数也可以,主要是要构造出一个序列做差统一分组:
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);
Posted by: 13POINTS at March 1, 2010 9:09 PM