【SQL】求两个日期值之间的工作天数

  • Post author:
  • Post category:其他


读书:《Oracle查询优化改写》后有感。

以emp表为例,我们需要求得hiredate的最大值与最小值之间的工作天数。

首先,我们需要求出max和min的hiredate:

SQL> select max(t.hiredate) as maxdate, min(t.hiredate) as mindate from emp t;
MAXDATE MINDATE
----------- -----------
1987/5/23 1980/12/17

SQL> 

其次,就是最关键的了:


要将min到max之间的日期全部展示出来,这里需要借助level,level的深度就是max到min之间的天数


(加不加1,看各人,反正我是没加):

select t1.mindate + (level - 1) as datestep
  from (select max(t.hiredate) as maxdate, min(t.hiredate) as mindate
          from emp t) t1
connect by level <= t1.maxdate - t1.mindate

结果:


(这里没有展示完全)

再然后根据to_char函数,将日期类型转换为星期,然后过滤即可:

SQL> select sum(case
  2               when to_char(t2.datestep, 'D') not in ('1', '7') then
  3                1
  4               else
  5                0
  6             end) as workdays
  7    from (select t1.mindate + (level - 1) as datestep
  8            from (select max(t.hiredate) as maxdate, min(t.hiredate) as mindate
  9                    from emp t) t1
 10          connect by level <= t1.maxdate - t1.mindate) t2;
  WORKDAYS
----------
      1678

SQL> 



版权声明:本文为q3dxdx原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。