让我们考虑一下六月和五月月， 6月1日是星期一，然后我将检查5月的第一个星期一，现在是5月4日。 像这样，我想按顺序匹配所有其他日子。
这就是我对这个问题的理解：
curmonCTE 创建当前月份prevmonCTE 上个月创建- 在最终查询(第 #13 行起(中，键是
NEXT_DAY函数
SQL> with
2 curmon as
3 (select trunc(sysdate, 'mm') + level - 1 as datum
4 from dual
5 connect by level <= last_day(sysdate) - trunc(sysdate, 'mm') + 1
6 ),
7 prevmon as
8 (select trunc(add_months(sysdate, -1), 'mm') + level - 1 as datum
9 from dual
10 connect by level <= last_day(add_months(sysdate, -1)) -
11 trunc(add_months(sysdate, -1), 'mm') + 1
12 )
13 select to_char(c.datum, 'dd.mm.yyyy, dy') cdatum,
14 --
15 to_char(next_day(p.datum, to_char(c.datum, 'dy')), 'dd.mm.yyyy, dy') result
16 from curmon c join prevmon p on to_char(c.datum, 'dd') = to_char(p.datum, 'dd')
17 order by c.datum;
CDATUM RESULT
--------------- ---------------
01.06.2020, mon 04.05.2020, mon
02.06.2020, tue 05.05.2020, tue
03.06.2020, wed 06.05.2020, wed
04.06.2020, thu 07.05.2020, thu
05.06.2020, fri 08.05.2020, fri
06.06.2020, sat 09.05.2020, sat
07.06.2020, sun 10.05.2020, sun
08.06.2020, mon 11.05.2020, mon
09.06.2020, tue 12.05.2020, tue
10.06.2020, wed 13.05.2020, wed
11.06.2020, thu 14.05.2020, thu
12.06.2020, fri 15.05.2020, fri
13.06.2020, sat 16.05.2020, sat
14.06.2020, sun 17.05.2020, sun
15.06.2020, mon 18.05.2020, mon
16.06.2020, tue 19.05.2020, tue
17.06.2020, wed 20.05.2020, wed
18.06.2020, thu 21.05.2020, thu
19.06.2020, fri 22.05.2020, fri
20.06.2020, sat 23.05.2020, sat
21.06.2020, sun 24.05.2020, sun
22.06.2020, mon 25.05.2020, mon
23.06.2020, tue 26.05.2020, tue
24.06.2020, wed 27.05.2020, wed
25.06.2020, thu 28.05.2020, thu
26.06.2020, fri 29.05.2020, fri
27.06.2020, sat 30.05.2020, sat
28.06.2020, sun 31.05.2020, sun
29.06.2020, mon 01.06.2020, mon
30.06.2020, tue 02.06.2020, tue
30 rows selected.
SQL>
截至您报告的错误("无效的列别名"(，您的数据库版本(它是什么？10g？(不支持此功能：
with curmon (datum) as --> "datum" here is what Oracle complains about
(select sysdate from dual)
但你必须把它写成
with curmon as
(select sysdate as datum from dual) --> alias moved here