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
;
1min目标场景问卷
立即参与
