那只是一个行生成器.例如:
样本数据:
SQL> with test (id, qty, status, start_date, end_date) as
2 (select 100, 345, 'Y', date '2023-01-01', date '2023-03-15' from dual union all
3 select 105, 784, 'N', date '2023-11-07', date '2024-01-28' from dual
4 )
查询(日期格式为DD.MM.YYYY):
5 select t.*,
6 to_char(add_months(start_date, column_value - 1), 'mm.yyyy') monthyear
7 from test t cross join
8 table(cast(multiset(select level from dual
9 connect by level <= ceil(months_between(end_date, start_date))
10 ) as sys.odcinumberlist));
ID QTY S START_DATE END_DATE MONTHYE
---------- ---------- - ---------- ---------- -------
100 345 Y 01.01.2023 15.03.2023 01.2023
100 345 Y 01.01.2023 15.03.2023 02.2023
100 345 Y 01.01.2023 15.03.2023 03.2023
105 784 N 07.11.2023 28.01.2024 11.2023
105 784 N 07.11.2023 28.01.2024 12.2023
105 784 N 07.11.2023 28.01.2024 01.2024
6 rows selected.
SQL>