Oracle Schdule的最近5个工作日(不包括星期六和星期日)

Question

我必须使用oracle schedule查找最近5个工作日。

select sysdate as current_date,
       case when to_char(sysdate,'D') in (1,2,7)
            then next_day(sysdate-7,'Friday')
            else sysdate-1 end as prev_weekday1,
       case when to_char(sysdate,'D') in (1,2,7)
            then next_day(sysdate-7,'Friday')
            else sysdate-2 end as prev_weekday2,
       case when to_char(sysdate,'D') in (1,2,7)
            then next_day(sysdate-7,'Friday')
            else sysdate-3 end as prev_weekday3,
       case when to_char(sysdate,'D') in (1,2,7)
            then next_day(sysdate-7,'Friday')
            else sysdate-4 end as prev_weekday4,
       case when to_char(sysdate,'D') in (1,2,7)
            then next_day(sysdate-7,'Friday')
            else sysdate-5 end as prev_weekday5
from dual

请帮助我是这种方式是正确的或任何其他优化的方式是有的。

Answer 1

select level from dual connect by level <=7为level提供结果1..7。

使用此选项，选择最近七天并删除星期六和星期日：

select 'prev_weekday_' || rownum as prev_weekday, d.dt
  from (select trunc(sysdate) - level dt,
               to_char(sysdate - level, 'D') w_day
          from dual
        connect by level <= 7) d
 where d.w_day <= 5
 order by d.dt desc;

/*
prev_weekday_1  14.07.2017
prev_weekday_2  13.07.2017
prev_weekday_3  12.07.2017
prev_weekday_4  11.07.2017
prev_weekday_5  10.07.2017
*/

如果您需要列中的结果，那么pivot：

select *
  from (select 'prev_weekday_' || rownum as prev_weekday, d.dt
          from (select trunc(sysdate) - level dt,
                       to_char(sysdate - level, 'D') w_day
                  from dual
                connect by level <= 7) d
         where d.w_day <= 5
         order by d.dt desc)
pivot(max(dt)
   for prev_weekday in('prev_weekday_1' as prev_weekday_1,
                       'prev_weekday_2' as prev_weekday_2,
                       'prev_weekday_3' as prev_weekday_3,
                       'prev_weekday_4' as prev_weekday_4,
                       'prev_weekday_5' as prev_weekday_5));
/*
prev_weekday_1  prev_weekday_2  prev_weekday_3  prev_weekday_4  prev_weekday_5
  14.07.2017     13.07.2017      12.07.2017      11.07.2017      10.07.2017
*/