WITH T1 AS( SELECT 'ZHANGSAN' T_PERSON_NAME, '01/JAN/2012' START_DT, '02/FEB/2013' END_DT FROM DUAL UNION ALL SELECT 'LISI' T_PERSON_NAME, '01/JAN/2012' START_DT, '02/FEB/2013' END_DT FROM DUAL) SELECT T_PERSON_NAME, TO_CHAR(ADD_MONTHS(START_DT, LEVEL - 1), 'yyyy/mm') MON FROM T1 CONNECT BY LEVEL <= MONTHS_BETWEEN(END_DT, START_DT) + 1 AND T_PERSON_NAME = PRIOR T_PERSON_NAME AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL ORDER BY T_PERSON_NAME, MON;
WITH T1 AS( SELECT 'ZHANGSAN' T_PERSON_NAME, '01/JAN/2012' START_DT, '02/FEB/2013' END_DT FROM DUAL UNION ALL SELECT 'LISI' T_PERSON_NAME, '01/JAN/2012' START_DT, '02/FEB/2013' END_DT FROM DUAL) SELECT T_PERSON_NAME, TO_CHAR(ADD_MONTHS(START_DT, LEVEL - 1), 'yyyy/mm') MON FROM T1 CONNECT BY LEVEL <= MONTHS_BETWEEN(END_DT, START_DT) + 1 AND T_PERSON_NAME = PRIOR T_PERSON_NAME AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL ORDER BY T_PERSON_NAME, MON;果然牛叉 谢谢
select 'TEST1' T_NAME, TO_CHAR(ADD_MONTHS(TO_DATE(BGN_YM, 'YYYY-MM-DD'), rnum - 1), 'YYYY-MM') MON --into TOTALVALUE from (select '2013-12-01' BGN_YM, '2014-10-01' END_YM, rownum rnum from ALL_TABLES) where rownum <= MONTHS_BETWEEN(TO_DATE(END_YM, 'YYYY-MM-DD'), TO_DATE(BGN_YM, 'YYYY-MM-DD'))
UNION select 'TEST2' T_NAME, TO_CHAR(ADD_MONTHS(TO_DATE(BGN_YM, 'YYYY-MM-DD'), rnum - 1), 'YYYY-MM') MON --into TOTALVALUE from (select '2013-12-01' BGN_YM, '2014-10-01' END_YM, rownum rnum from ALL_TABLES) where rownum <= MONTHS_BETWEEN(TO_DATE(END_YM, 'YYYY-MM-DD'), TO_DATE(BGN_YM, 'YYYY-MM-DD'))
上海 2013-10-01 2014-4-01
这2条数据在同一个表中 不存在什么表1, 表2。请仔细斟酌表2实际上就是表1,只不过要自内联下,为了区分order by后的语句...
WITH T1 AS(
SELECT 'ZHANGSAN' T_PERSON_NAME,
'01/JAN/2012' START_DT,
'02/FEB/2013' END_DT
FROM DUAL
UNION ALL
SELECT 'LISI' T_PERSON_NAME, '01/JAN/2012' START_DT, '02/FEB/2013' END_DT
FROM DUAL)
SELECT T_PERSON_NAME,
TO_CHAR(ADD_MONTHS(START_DT, LEVEL - 1), 'yyyy/mm') MON
FROM T1
CONNECT BY LEVEL <= MONTHS_BETWEEN(END_DT, START_DT) + 1
AND T_PERSON_NAME = PRIOR T_PERSON_NAME
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
ORDER BY T_PERSON_NAME, MON;
WITH T1 AS(
SELECT 'ZHANGSAN' T_PERSON_NAME,
'01/JAN/2012' START_DT,
'02/FEB/2013' END_DT
FROM DUAL
UNION ALL
SELECT 'LISI' T_PERSON_NAME, '01/JAN/2012' START_DT, '02/FEB/2013' END_DT
FROM DUAL)
SELECT T_PERSON_NAME,
TO_CHAR(ADD_MONTHS(START_DT, LEVEL - 1), 'yyyy/mm') MON
FROM T1
CONNECT BY LEVEL <= MONTHS_BETWEEN(END_DT, START_DT) + 1
AND T_PERSON_NAME = PRIOR T_PERSON_NAME
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
ORDER BY T_PERSON_NAME, MON;果然牛叉 谢谢
TO_CHAR(ADD_MONTHS(TO_DATE(BGN_YM, 'YYYY-MM-DD'), rnum - 1),
'YYYY-MM') MON
--into TOTALVALUE
from (select '2013-12-01' BGN_YM, '2014-10-01' END_YM, rownum rnum
from ALL_TABLES)
where rownum <= MONTHS_BETWEEN(TO_DATE(END_YM, 'YYYY-MM-DD'),
TO_DATE(BGN_YM, 'YYYY-MM-DD'))
UNION
select 'TEST2' T_NAME,
TO_CHAR(ADD_MONTHS(TO_DATE(BGN_YM, 'YYYY-MM-DD'), rnum - 1),
'YYYY-MM') MON
--into TOTALVALUE
from (select '2013-12-01' BGN_YM, '2014-10-01' END_YM, rownum rnum
from ALL_TABLES)
where rownum <= MONTHS_BETWEEN(TO_DATE(END_YM, 'YYYY-MM-DD'),
TO_DATE(BGN_YM, 'YYYY-MM-DD'))