求一个关于时间的查询
我有一个表
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