select
???? min(to_char(day, 'yyyymm')) month,
???? min(decode(weekday, 1, day)) "星期日"
???? min(decode(weekday, 2, day)) "星期一",
???? min(decode(weekday, 3, day)) "星期二",
???? min(decode(weekday, 4, day)) "星期三",
???? min(decode(weekday, 5, day)) "星期四",
???? min(decode(weekday, 6, day)) "星期五",
???? min(decode(weekday, 7, day)) "星期六",
???? from (select day,
????????????????? month,
????????????????? decode(sign(rn - weekday), 1, week + 1, week) week,
????????????????? weekday,
????????????????? rn
???????????? from (select day,
????????????????????????? to_char(day, 'mm') month,
????????????????????????? to_char(day, 'w') week,
????????????????????????? to_char(day, 'd') weekday,
????????????????????????? row_number() over(partition by to_char(day, 'mm'), to_char(day, 'w') order by day) rn
???????????????????? from (select trunc(sysdate, 'yyyy') + level - 1 day
???????????????????????????? from dual
?????????????????????????? connect by rownum <= trunc(sysdate+365,'yyyy')-trunc(sysdate,'yyyy')))) a
??? group by a.month, a.week
??? order by a.month, a.week;