oralce sql 创建指定时间段内的日历信息

创建 开始时间是2010 截止时间是2100的日历表数据。



create table temp_calendar as select

   min(to_char(day, ‘yyyymm‘)) month,
   to_char(min(decode(weekday, 1, day)) ,‘dd‘) w_7,
   to_char(min(decode(weekday, 2, day)),‘dd‘) w_1,
   to_char(min(decode(weekday, 3, day)),‘dd‘) w_2,
   to_char(min(decode(weekday, 4, day)),‘dd‘) w_3,
   to_char(min(decode(weekday, 5, day)),‘dd‘) w_4,
   to_char(min(decode(weekday, 6, day)),‘dd‘) w_5,
   to_char(min(decode(weekday, 7, day)),‘dd‘) w_6
  from (select trunc(day,‘yyyy‘) year,
                 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(to_date(‘2008‘,‘yyyy‘), ‘yyyy‘) + level - 1 day
               from dual
              connect by rownum <= trunc(to_date(‘2101‘,‘yyyy‘),‘yyyy‘)-trunc(to_date(‘2008‘,‘yyyy‘),‘yyyy‘)))) a
  group by a.year,a.month, a.week
  order by a.year,a.month, a.week;

oralce sql 创建指定时间段内的日历信息

上一篇:SQL Server数据库存储过程中拼接字符串注意的问题


下一篇:mysql 查看表的类型(转)