它是行生成器,但不像你那样做;很可能您在我的查询(或其替代方案)中遗漏了第 11 - 16 行。
SQL> with test (item,date_from,date_to) as
2 -- sample data
3 (select 'A',date '2018-01-03',date '2018-03-16' from dual union all
4 select 'B',date '2021-05-25',date '2021-11-10' from dual
5 )
6 -- query that returns desired result
7 select item,8 extract(month from (add_months(date_from,column_value - 1))) month,9 extract(year from (add_months(date_from,column_value - 1))) year
10 from test cross join
11 table(cast(multiset
12 (select level
13 from dual
14 connect by level <=
15 months_between(trunc(least(sysdate,date_to),'mm'),trunc(date_from,'mm')) + 1
16 ) as sys.odcinumberlist))
17 order by item,year,month;
ITEM MONTH YEAR
----- ---------- ----------
A 1 2018
A 2 2018
A 3 2018
B 5 2021
B 6 2021
B 7 2021
B 8 2021
7 rows selected.
SQL>
,
递归 CTE 是解决此类问题的标准 SQL 方法。在 Oracle 中,这看起来像:
with cte(item,fromd,tod) as (
select item,tod
from t
union all
select item,add_months(fromd,1),tod
from cte
where add_months(fromd,1) < last_day(tod)
)
select item,extract(year from fromd) as year,extract(month from fromd) as month
from cte
order by item,fromd;
Here 是一个 dbfiddle。
本文链接:https://www.f2er.com/2837.html