oracle合并一天的数据,oracle数据库！连续时间合并显示！

with tab1 as (

select '201109' st, '201201' ed, '机电1120' clazz from dual union all

select '201203' st, '201206' ed, '机电1120' clazz from dual union all

select '201209' st, '201301' ed, '机电1120' clazz from dual union all

select '201509' st, '201601' ed, '机电1120' clazz from dual union all

select '201603' st, '201606' ed, '机电1120' clazz from dual union all

select '201109' st, '201201' ed, '机电3143' clazz from dual union all

select '201203' st, '201206' ed, '机电3143' clazz from dual union all

select '201309' st, '201401' ed, '机电3143' clazz from dual

)

, tab2 as (

select t1.*,

decode(months_between(to_date(st, 'yyyymm'), to_date(lag(t1.st) over(partition by t1.clazz order by t1.st), 'yyyymm')), 6, null, 1) lg

from tab1 t1

order by t1.clazz, t1.st

)

, tab3 as (

select t1.*,

sum(t1.lg) over(partition by t1.clazz order by t1.st) sm

from tab2 t1

)

select t1.clazz, t1.sm, min(t1.st)kssj, max(t1.ed)jssj

from tab3 t1

group by t1.clazz, t1.sm

order by t1.clazz, t1.sm

;