oracle 将时间列表按规则拆分成时间段

[dyb1415]

表1

NY GYRS
201509 2
201510 2
201511 3
201512 3
201601 3
201602 2
201603 1
201604 1
201605 1

表2
kssj zzsj
201301 201312
201401 201412
201501 201512
201601

根据表1的GYRS变化和表2的时间段，得到如下结果

kssj zzsj gyrs
201509 201510 2
201511 201512 3
201601 201601 3
201602 201602 2
201603 201605 1

[dyb1415]

引用 1 楼 nie544917623 的回复:

select MIN(NY) kssj,MAX(NY) zzsj,gyrs from 表1 group by substr(ny,1,4),gyrs order by kssj;

这个查询不能用 如果同一年不同月数但是GYRS一样就不对了 表1 NY GYRS 201509 2 201510 2 201511 3 201512 3 201601 3 201602 2 201603 1 201604 1 201605 3 表2 kssj zzsj 201301 201312 201401 201412 201501 201512 201601 根据表1的GYRS变化和表2的时间段，得到如下结果 kssj zzsj gyrs 201509 201510 2 201511 201512 3 201601 201601 3 201602 201602 2 201603 201604 1 201605 3

[覌海雲逺]

select MIN(NY) kssj,MAX(NY) zzsj,gyrs from (select * from 表1 order by gyrs,ny) group by substr(ny,1,4),gyrs,ny-rownum order by kssj;

[dyb1415]

求帮助 实在想不出来了

[覌海雲逺]

引用 3 楼 nie544917623 的回复:

select MIN(NY) kssj,MAX(NY) zzsj,gyrs from (select * from 表1 order by gyrs,ny) group by substr(ny,1,4),gyrs,ny-rownum order by kssj;

select MIN(NY) kssj,MAX(NY) zzsj,gyrs from (select * from 表1 order by gyrs,ny) group by gyrs,ny-rownum order by kssj; 这样更省事，ny-rownum已经包含substr(ny,1,4)了

[ghx287524027]

这有一个，但是还有一个小问题

select min(ny) as kssj,max(ny) as zzsj,gyrs from(
	select ny,gyrs,r,row_number() over(partition by gyrs,r order by ny)-rownum d from(
		select ny,gyrs,(select r from (select kssj,zzsj,rownum r from t2) where t1.ny<=zzsj and t1.ny>=kssj) r  from t1 order by ny
	)
) 
group by gyrs,r,d
order by kssj asc;

结果如下：

最后的那个201605没有设置为空

[dyb1415]

引用 5 楼 nie544917623 的回复:

[quote=引用 3 楼 nie544917623 的回复:] select MIN(NY) kssj,MAX(NY) zzsj,gyrs from (select * from 表1 order by gyrs,ny) group by substr(ny,1,4),gyrs,ny-rownum order by kssj;

select MIN(NY) kssj,MAX(NY) zzsj,gyrs from (select * from 表1 order by gyrs,ny) group by gyrs,ny-rownum order by kssj; 这样更省事，ny-rownum已经包含substr(ny,1,4)了[/quote] 解决了该问题，万分感谢~

[dyb1415]

引用 5 楼 nie544917623 的回复:

[quote=引用 3 楼 nie544917623 的回复:] select MIN(NY) kssj,MAX(NY) zzsj,gyrs from (select * from 表1 order by gyrs,ny) group by substr(ny,1,4),gyrs,ny-rownum order by kssj;

select MIN(NY) kssj,MAX(NY) zzsj,gyrs from (select * from 表1 order by gyrs,ny) group by gyrs,ny-rownum order by kssj; 这样更省事，ny-rownum已经包含substr(ny,1,4)了[/quote] 解决了该问题，万分感谢~

[dyb1415]

引用 6 楼 ghx287524027 的回复:

这有一个，但是还有一个小问题

select min(ny) as kssj,max(ny) as zzsj,gyrs from(
	select ny,gyrs,r,row_number() over(partition by gyrs,r order by ny)-rownum d from(
		select ny,gyrs,(select r from (select kssj,zzsj,rownum r from t2) where t1.ny<=zzsj and t1.ny>=kssj) r  from t1 order by ny
	)
) 
group by gyrs,r,d
order by kssj asc;

结果如下： 最后的那个201605没有设置为空

解决了该问题，万分感谢

[覌海雲逺]

select MIN(NY) kssj,MAX(NY) zzsj,gyrs from 表1 group by substr(ny,1,4),gyrs order by kssj;