求一个关于时间的查询
我有一个表

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




------解决方案--------------------
SELECT NAME,
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