Given:
鉴于：
| ID_Composite1 | ID_Composite2 | DateStart | DateEnd |
| 1 | 2 | 11-24-2015| 03-11-2016|
| 2 | 4 | 8-11-2015 | 12-11-2015|
I need to get:
我需要得到：
| ID_Composite1 | ID_Composite2 | Month |
| 1 | 2 | 11-24-2015|
| 1 | 2 | 12-24-2015|
| 1 | 2 | 1-24-2016|
| 1 | 2 | 2-24-2016|
| 1 | 2 | 3-11-2016|
| 2 | 4 | 8-11-2015 |
| 2 | 4 | 9-11-2015 |
| 2 | 4 | 9-11-2015 |
I tried the most simplified case (generate a range between only months using level and connect by) but I can't get it working:
我尝试了最简化的情况（在使用级别和连接的几个月之间生成一个范围）但我无法使其工作：
SQLFiddle
1 个解决方案
#1
0
So, I forced an iterator on your SQL, to make sure there will be enough rows to make things work properly....
所以，我强制你的SQL上有一个迭代器，以确保有足够的行来使事情正常工作....
check it out:
一探究竟：
SELECT firstcompositekey, secondcompositekey, to_char(add_months(startmonth, iterator),'MM-YYYY') as month
FROM test,
(SELECT LEVEL iterator FROM dual CONNECT BY LEVEL <= 1000) iterations
WHERE add_months(startmonth, iterator) <= endmonth
ORDER BY 1,3
Here is the SQLFiddle
这是SQLFiddle
Hope this helps
希望这可以帮助
#1
0
So, I forced an iterator on your SQL, to make sure there will be enough rows to make things work properly....
所以，我强制你的SQL上有一个迭代器，以确保有足够的行来使事情正常工作....
check it out:
一探究竟：
SELECT firstcompositekey, secondcompositekey, to_char(add_months(startmonth, iterator),'MM-YYYY') as month
FROM test,
(SELECT LEVEL iterator FROM dual CONNECT BY LEVEL <= 1000) iterations
WHERE add_months(startmonth, iterator) <= endmonth
ORDER BY 1,3
Here is the SQLFiddle
这是SQLFiddle
Hope this helps
希望这可以帮助