如果你有一个以上的名字和一个日期,你可以这样做,但只有当输入的名字是不同的工作。(否则,可以修改查询以适应非唯一名称。)
奇怪的是,您希望调用月末日期
start_date
,与输入数据中相同。而且
name
with
test_data ( name, start_date ) as (
select 'Fly', to_date('01-Jul-16', 'dd-Mon-yy') from dual union all
select 'Two', to_date('15-Feb-17', 'dd-Mon-yy') from dual
)
-- End of simulated inputs (for testing only, not part of the solution).
-- Use your actual table and column names in the SQL below.
select name, add_months(last_day(start_date), level - 1) as start_date
from test_data
connect by add_months(last_day(start_date), level - 1) <= last_day(sysdate)
and prior name = name
and prior sys_guid() is not null
order by name, start_date
;
输出
NAME START_DATE
--- ----------
Fly 31-Jul-16
Fly 31-Aug-16
Fly 30-Sep-16
Fly 31-Oct-16
Fly 30-Nov-16
Fly 31-Dec-16
Fly 31-Jan-17
Fly 28-Feb-17
Fly 31-Mar-17
Fly 30-Apr-17
Fly 31-May-17
Fly 30-Jun-17
Fly 31-Jul-17
Fly 31-Aug-17
Two 28-Feb-17
Two 31-Mar-17
Two 30-Apr-17
Two 31-May-17
Two 30-Jun-17
Two 31-Jul-17
Two 31-Aug-17