一个查询不连续数字记录的问题

wspwhgwyh 2014-01-20 10:30:57

2现在表A中有个字段，比如year.值是一些年月。比如200406，200407，200408，200509，200510，200511，200512,200601,200609当中有断开。需要查询这些数据中连续的日期段和断开的日期段。比如连续段为200406-200408，200509-200601；不连续日期段为200409-200508，200602-200608
请教了，大家有没有什么好方法。

...全文

195 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

cxyy_ 2014-01-20

打赏
举报

select 
		gp_year,min(years)||'-'||max(years)
from
(
		select 
		  a.years
		  ,decode(a.year,b.years+1,seq_test.nextval,seq_test.currval) gp_year 
		from （select *,row_num() _rows from t order by years） a
		  	left join（select ,row_num() _rows from t order by years）b
		 		on a.rows = (b.rows+1)
) years_group  
group by gp_year

cxyy_ 2014-01-20

打赏
举报

大致思路是这，细节上改改 select gp_year,min(years)||'-'||max(years) from ( select a.years ,decode(a.year,b.years+1,seq_test.nextval,seq_test.currval) gp_year from （select *,row_num() _rows from t order by years） a left join（select ,row_num() _rows from t order by years）b on a.rows = (b.rows+1) ) years_group group by gp_year

大话EPM 2014-01-20

打赏
举报

ref： 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 /

cxyy_ 2014-01-20

打赏
举报

seq_test是序列，需先定义才能使用

wspwhgwyh 2014-01-20

打赏
举报

按你所写的修改了一下 select gp_year, min(years) || '-' || max(years) from (select a.years, decode(a.years, b.years+ 1, seq_test.nextval, seq_test.currval) gp_year from (select years, rownum rows1 from t order by years) a left join (select rownum rows1 from t order by years) b on a.rows1 = (b.rows1 + 1)) year_group group by gp_year 为什么seq_test.nextval,seq_test.currval在这报不允许序号呢？