原表 数据如下：
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
求高手赐教。。。。。
------解决方案--------------------
- SQL code
--原表with tb1(ID ,start_month ,months) as(select 1 ,'201201' ,2 from dual union allselect 2 ,'201205' ,1 from dual union allselect 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
------解决方案--------------------
- SQL code
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.rnORDER BY t1.tid,month_detail
------解决方案--------------------
这个是正确思路，我再给你写一个
- SQL code
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