oracle时间相减得到天数_题目2：关于计日历表中工作天数的方法

    今天在群里看到一位小伙伴提问关于写自定义函数的问题，当时是在语句块的if语句后面使用了exists ，导致编译不通过。后来询问之后是这样的一个需求，有一张日历表，表中存放着公司的休息日，大概就是这样:

    大概表结构是这样，现在是让传入一个开始时间和结束时间，求这两个时间之内的工作天数：

    比如传入开始时间为 2020-01-02  结束时间为 2020-01-28 则最终得到的时间为21天

     开始他的想法是用循环，将 2020-01-02-2020-01-28 这27天逐一传入判断表中是否包含，代码如下，原理也很简单 每次判断表中有没有此数据，有的话就是休息日，i不加1 ，没有的话就是休息日 i+1,这样循环27次后，就可以得到我们想要的数据了。

create or replace function gzrts(dt_begin in date, dt_end in date)  return number as  i number := 0;  j number := 0;  s date := dt_begin;  a number;begin  while s <= dt_end   loop    select count(1)      into a      from t1 m     where m.begin_date <= s       and m.end_date >= s;    if a = 1 then      j := j + 1;    else      i :=i + 1;    end if;    s := s + 1;  end loop;  return i;end;

    可以看到我们查询后的数据也是21

    上面是第一种方案，然后我就有一种预感，这种逻辑应该是可以通过一条sql直接查询出来的，后来的方案是这样

select max(end_date) - min(begin_date) - sum(cnt) + 1 as cnt-- cnt为黑色方框 也就是休息日的长度  from (select begin_date, end_date, end_date - begin_date + 1 as cnt          from t1         where begin_date <= date '2020-01-28'           and end_date >= date '2020-01-02'        union all        select date '2020-01-02' as begin_date,               date '2020-01-28' as end_date,               0    as cnt          from dual)

    大家猛的一看可能会有点猛，当时是想到了一种一维坐标，一个完整的日历就是一条线，然后再这条线上分布着许多休息日，如图所示，黑色的方框就是休息日，两条黄色的线就是我们的开始日期和截止日期，中间橙色的部分就是我们需要得工作日，目前我们已经知道的是上面的黑色和黄色线。要求出来下面的橙色部分，通过观察可以看出来，使用上面部分的最大值减去最小值，然后再减去黑色方框部分，就可以得到下面的橙色的长度。

    但是有一点，就是开始的线和截止的线如果落在了黑色方框上，我们求的时候就需要单独将其标记，明显不符合我们的通用查询需求，所以在这个地方我们让开始线上的黑色部分的前半部分也显示出来，假如结束线也落在了黑色方框。则让结束线的后面部分也显示出来，这个就得到了我们需要的条件

传入的起始时间传入的结束时间>begin_date(表中的结束时间)-- 这里需要大家思考一下，只有这样的条件才能让最大值和最小值固定

    上述是第一步，第二步就是让开始时间早于黑色方框的部分也显示出来，这里是没有这一部分。也让结束时间晚于黑色方框的部分显示出来，在这里就是2020-01-25到2020-01-28这一段时间，所以我们将上面的查询结果并上我们输入的开始时间和结束时间

 union all        select date '2020-01-02' as begin_date,               date '2020-01-28' as end_date,               0    as cnt          from dual--- 在这里，上面代码的cnt是黑色方框的长度，这一部分的cnt是为了防止代码报错

    第三步就是将全部长度(最大值减去最小值)-休息日的长度(黑色方框长度之和)+1(临界值1)，就得到了最终的代码。这一段代码可以直接用于查询，也可以将其封装在一个函数中用于调用，代码如下

create or replace function  gzrts1(dt_begin in date, dt_end in date)  return number as  i number := 0;begin  select max(end_date) - min(begin_date) - sum(cnt) + 1    into i    from (select begin_date, end_date, end_date - begin_date + 1 as cnt            from t1           where begin_date <= dt_end             and end_date >= dt_begin          union all          select dt_begin as begin_date, dt_end as end_date, 0 as cnt            from dual);  return i;end;

    就这样，两种方法都可以解决这个问题，一种是逐条匹配，一种是整体计算。大家有其他好的方法也可以给作者留言呦~

    知识点：

        1.oracle数据库中日期可以直接相减，得到日期差，单位是天

        2.oracle数据库中日期也可以使用比较运算符进行比较

        3.dual表是一张系统表，用于测试函数等，表中只有一行

        4.在语句块中 使用这样的赋值方式是不对的 

select 1 into a from dual where 1=2

    今天碰到了这个问题，所以先给大家讲解了这个问题的解答思路，昨天那个同样成绩的同学排名的处理方案下一篇文章给大家进行解答~

    刚开始编写文章，很多地方都有不足，希望以后可以为大家贡献出更好的文章，谢谢大家！

    ps:还没有讲解如何安装数据库软件，我会在公众号的菜单栏中添加了安装教程供大家安装，谢谢，碰到安装上的问题也可以给作者发消息或者留言~~

                      感觉还不错的话，点下在看鼓励一下作者吧  

   没有关注的也可以关注下公众号~再次感谢