原表 数据如下:ID start_month months
1 201201 2
2 201205 1
3 201203 12要求各ID根据开始年月和月数来构造每一个月的分解查询,
查询结果如下:ID start_month months month_DETAIL
1 201201 2 201201
1 201201 2 201202
2 201205 1 201205
3 201203 12 201203
3 201203 12 201204
3 201203 12 201205
3 201203 12 201206
3 201203 12 201207
3 201203 12 201208
3 201203 12 201209
3 201203 12 201210
3 201203 12 201211
3 201203 12 201212
3 201203 12 201301
3 201203 12 201302求高手赐教。。
1 201201 2
2 201205 1
3 201203 12要求各ID根据开始年月和月数来构造每一个月的分解查询,
查询结果如下:ID start_month months month_DETAIL
1 201201 2 201201
1 201201 2 201202
2 201205 1 201205
3 201203 12 201203
3 201203 12 201204
3 201203 12 201205
3 201203 12 201206
3 201203 12 201207
3 201203 12 201208
3 201203 12 201209
3 201203 12 201210
3 201203 12 201211
3 201203 12 201212
3 201203 12 201301
3 201203 12 201302求高手赐教。。
with tb1(ID ,start_month ,months) as
(select 1 ,'201201' ,2 from dual union all
select 2 ,'201205' ,1 from dual union all
select 3 ,'201203' ,12 from dual)
--原表基础上多构造了一个end_month
,tb2(ID ,start_month,end_month ,months) as
(select ID, start_month,to_char(add_months(to_date(start_month,'yyyymm'),months-1),'yyyymm'),months from tb1)
--构造了一个最长月的时间段201201-201302(14个月)
,tb3 ( month_DETAIL ) as
(select to_char(add_months(to_date((select min(start_month) from tb2),'yyyymm'),rownum-1),'yyyymm') from dual
connect by rownum<=(select months_between(to_date(max(end_month),'yyyymm'),to_date(min(start_month),'yyyymm')) from tb2)+1)
--tb2,tb3做连接,连接条件是month_DETAIL的值在start_month,end_month之间(包含)
select ID,start_month,months, month_DETAIL from tb2,tb3 where month_DETAIL between start_month and end_month order by id
SELECT t1.tid,
t1.start_month,
t1.months,
TO_CHAR(ADD_MONTHS(TO_DATE(t1.start_month,'yyyymm'),t2.rn - 1),'yyyymm') month_detail
FROM tb t1,
(SELECT ROWNUM rn,
max_mon
FROM (SELECT MAX(months) max_mon
FROM tb) m
CONNECT BY ROWNUM <= max_mon) t2
WHERE t1.months >= t2.rn
ORDER BY t1.tid,month_detail
select a,b,c, lpad(0+rownum,2,'0') d From
(select 1 a, 201201 b, 2 c from dual
union
select 2 a, 201205 b,1 c from dual
union
select 3 a, 201203 b, 12 c from dual) x
connect by rownum<=x.c;
SELECT T1.TID,
T1.START_MONTH,
T1.MONTHS,
TO_CHAR(ADD_MONTHS(TO_DATE(T1.START_MONTH, 'yyyymm'), L), 'yyyymm') MONTH_DETAIL
FROM 原表 T1,
(SELECT LEVEL L
FROM DUAL
CONNECT BY LEVEL <= (SELECT MAX(MONTHS) MAX_MON FROM 原表)) T2
WHERE T1.MONTHS >= T2.L
ORDER BY T1.TID, MONTH_DETAIL