求一个关于时间的查询
我有一个表
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
------------------------------------
NAMEYEARMONTHAMOUNT
1aaaa200651234
2aaaa200661234
3aaaa200671234
4aaaa200681234
5aaaa200691234
6aaaa2006101234
7bbbb200775678
8bbbb200785678
9bbbb200795678
