我有一个表id name start_time end_time amount
-------------------------------------------------
01 aaaa 2006-5-1 2006-10-1 1234
02 bbbb 2007-7-1 2007-9-1 5678我想要写一个视图,查询后是这样的name year month amount
---------------------------------
aaaa 2006 5 1234
aaaa 2006 6 1234
aaaa 2006 7 1234
aaaa 2006 8 1234
aaaa 2006 9 1234
aaaa 2006 10 1234
bbbb 2007 7 5678
bbbb 2007 8 5678
bbbb 2007 9 5678
-------------------------------------------------
01 aaaa 2006-5-1 2006-10-1 1234
02 bbbb 2007-7-1 2007-9-1 5678我想要写一个视图,查询后是这样的name year month amount
---------------------------------
aaaa 2006 5 1234
aaaa 2006 6 1234
aaaa 2006 7 1234
aaaa 2006 8 1234
aaaa 2006 9 1234
aaaa 2006 10 1234
bbbb 2007 7 5678
bbbb 2007 8 5678
bbbb 2007 9 5678
to_number(to_char(add_months(a.start_time,
months_between(end_time, start_time) - b.rn + 1),
'YYYY')) AS YEAR,
to_number(to_char(add_months(a.start_time,
months_between(end_time, start_time) - b.rn + 1),
'MM')) AS MONTH, a.amount
FROM (SELECT 'aaaa' AS NAME, to_date('2006-5-1', 'YYYY-MM-DD') AS start_time,
to_date('2006-10-1', 'YYYY-MM-DD') AS end_time, 1234 AS amount
FROM dual
UNION ALL
SELECT 'bbbb' AS NAME, to_date('2007-7-1', 'YYYY-MM-DD') AS start_time,
to_date('2007-9-1', 'YYYY-MM-DD'), 5678 AS amount
FROM dual) a, (SELECT rownum AS rn FROM user_tab_cols) b
WHERE b.rn - 1 <= months_between(end_time, start_time)
ORDER BY NAME, MONTH------------------------------------------------------------------------
NAME YEAR MONTH AMOUNT
1 aaaa 2006 5 1234
2 aaaa 2006 6 1234
3 aaaa 2006 7 1234
4 aaaa 2006 8 1234
5 aaaa 2006 9 1234
6 aaaa 2006 10 1234
7 bbbb 2007 7 5678
8 bbbb 2007 8 5678
9 bbbb 2007 9 5678