SQL 计算两个日期之间的工作日天数|极客教程

SQL 计算两个日期之间的工作日天数，给定两个日期，你想知道两者之间有多少个工作日，并且两个日期自身也要计算进去。例如，如果 1 月 10 日是星期一，1 月 11 日是星期二，则两者之间的工作日个数是 2，这是因为两个日期都是工作日。对于本实例而言，“工作日”定义为除了星期六和星期日以外的日子。

SQL 计算两个日期之间的工作日天数问题描述

给定两个日期，你想知道两者之间有多少个工作日，并且两个日期自身也要计算进去。例如，如果 1 月 10 日是星期一，1 月 11 日是星期二，则两者之间的工作日个数是 2，这是因为两个日期都是工作日。对于本实例而言，“工作日”定义为除了星期六和星期日以外的日子。

SQL 计算两个日期之间的工作日天数解决方案

下面的解决方案以找出 BLAKE 和 JONES 的 HIREDATE 之间有多少个工作日为例。为了计算两个日期之间的工作日天数，我们可以使用数据透视表，把两个日期之间的每一天（包括开始和结束日期）都作为单独的一行返回。这样的话，统计工作日天数就变成了计算有多少个既不是星期六又不是星期日的日期。

如果想要把假日也排除在外，就需要创建一个 `HOLIDAYS` 表。然后在本解决方案基础上，使用 `NOT IN` 排除掉 `HOLIDAYS` 表里列出的日期。

DB2
使用数据透视表 T500 生成所需要的行数（两个日期之间的天数）的结果集。然后统计非周末日期的个数。使用 DAYNAME 函数能够知道一个日期是星期几。例如：

 1  select sum(case when dayname(jones_hd+t500.id day -1 day)
 2                    in ( 'Saturday','Sunday' )
 3                  then 0 else 1
 4             end) as days
 5    from (
 6  select max(case when ename = 'BLAKE'
 7                  then hiredate
 8             end) as blake_hd,
 9         max(case when ename = 'JONES'
10                  then hiredate
11             end) as jones_hd
12    from emp
13   where ename in ( 'BLAKE','JONES' )
14          ) x,
15          t500
16   where t500.id <= blake_hd-jones_hd+1

MySQL
使用数据透视表 T500 生成所需要的行数（两个日期之间的天数）的结果集，然后统计非周末日期的个数。使用 DATE_ADD 函数为每一个日期加上若干天。使用 DATE_FORMAT 函数能够知道一个日期是星期几。

 1  select sum(case when date_format(
 2                          date_add(jones_hd,
 3                                   interval t500.id-1 DAY),'%a')
 4                    in ( 'Sat','Sun' )
 5                  then 0 else 1
 6             end) as days
 7    from (
 8  select max(case when ename = 'BLAKE'
 9                  then hiredate
10             end) as blake_hd,
11          max(case when ename = 'JONES'
12                   then hiredate
13              end) as jones_hd
14    from emp
15   where ename in ( 'BLAKE','JONES' )
16         ) x,
17         t500
18   where t500.id <= datediff(blake_hd,jones_hd)+1

Oracle
使用数据透视表 T500 生成所需要的行数（两个日期之间的天数）的结果集，然后统计非周末日期的个数。使用 TO_CHAR 函数能够知道一个日期是星期几。

 1  select sum(case when to_char(jones_hd+t500.id-1,'DY')
 2                    in ( 'SAT','SUN' )
 3                  then 0 else 1
 4             end) as days
 5    from (
 6  select max(case when ename = 'BLAKE'
 7                  then hiredate
 8             end) as blake_hd,
 9         max(case when ename = 'JONES'
10                  then hiredate
11             end) as jones_hd
12    from emp
13   where ename in ( 'BLAKE','JONES' )
14         ) x,
15         t500
16   where t500.id <= blake_hd-jones_hd+1

PostgreSQL
使用数据透视表 T500 生成所需要的行数（两个日期之间的天数）的结果集，然后统计非周末日期的个数。使用 TO_CHAR 函数能够知道一个日期是星期几。

 1  select sum(case when trim(to_char(jones_hd+t500.id-1,'DAY'))
 2                    in ( 'SATURDAY','SUNDAY' )
 3                  then 0 else 1
 4             end) as days
 5    from (
 6  select max(case when ename = 'BLAKE'
 7                  then hiredate
 8             end) as blake_hd,
 9         max(case when ename = 'JONES'
10                  then hiredate
11             end) as jones_hd
12    from emp
13   where ename in ( 'BLAKE','JONES' )
14         ) x,
15         t500
16   where t500.id <= blake_hd-jones_hd+1

SQL Server
使用数据透视表 T500 生成所需要的行数（两个日期之间的天数）的结果集，然后统计非周末日期的个数。使用 DATENAME 函数能够知道一个日期是星期几。

 1  select sum(case when datename(dw,jones_hd+t500.id-1)
 2                    in ( 'SATURDAY','SUNDAY' )
 3                   then 0 else 1
 4             end) as days
 5    from (
 6  select max(case when ename = 'BLAKE'
 7                  then hiredate
 8             end) as blake_hd,
 9         max(case when ename = 'JONES'
10                  then hiredate
11             end) as jones_hd
12    from emp
13   where ename in ( 'BLAKE','JONES' )
14         ) x,
15         t500
16   where t500.id <= datediff(day,jones_hd-blake_hd)+1

SQL 计算两个日期之间的工作日天数扩展知识

尽管不同数据库要调用不同的内置函数来确认一个日期是星期几，但上述解决方案的思路都是相同的。具体做法分为两步：
(1) 计算出开始日期和结束日期之间相隔多少天（包含开始日期和结束日期）；
(2) 排除掉周末，统计有多少个工作日（实际是在计算有多少条记录）。
内嵌视图 X 负责完成第一步工作。仔细查看内嵌视图 X 的话，我们会注意到它使用了聚合函数 MAX，其目的在于排除掉 Null。如果不了解 MAX 的用处，下面的查询结果输出能帮我们加深理解。下面的输出内容展示了没有 MAX 函数的内嵌视图 X 的查询结果。

select case when ename = 'BLAKE'
            then hiredate
       end as blake_hd,
       case when ename = 'JONES'
            then hiredate
       end as jones_hd
  from emp
 where ename in ( 'BLAKE','JONES' )
　
BLAKE_HD    JONES_HD
----------- -----------
            02-APR-1981
01-MAY-1981

如果不调用 MAX 函数，会有两行查询结果。有了 MAX 函数，查询结果就是一行，Null 会被过滤掉。

select max(case when ename = 'BLAKE'
            then hiredate
       end) as blake_hd,
       max(case when ename = 'JONES'
            then hiredate
       end) as jones_hd
  from emp
 where ename in ( 'BLAKE','JONES' )
　
BLAKE_HD    JONES_HD
----------- -----------
01-MAY-1981 02-APR-1981

两个日期之间相差 30 天（包含开始日期和结束日期）。现在，我们在同一行中得到了两个日期，下一步就是为这 30 天里的每一天单独生成一行记录。为了得到 30 天（行）的记录，这里用到了 T500 表。T500 表的 ID 列每一个值都等于前面一行的值加上 1，在两个日期中较早的那个（JONES_HD）的基础上依次加上 T500 表对应的值，这样就生成了 JONES_HD 和 BLAKE_HD 之间的连续日期序列。结果如下所示（使用 Oracle 语法）。

select x.*, t500.*, jones_hd+t500.id-1
  from (
select max(case when ename = 'BLAKE'
                then hiredate
           end) as blake_hd,
       max(case when ename = 'JONES'
                then hiredate
           end) as jones_hd
  from emp
 where ename in ( 'BLAKE','JONES' )
       ) x,
       t500
 where t500.id <= blake_hd-jones_hd+1
　
BLAKE_HD    JONES_HD            ID JONES_HD+T5
----------- ----------- ---------- -----------
01-MAY-1981 02-APR-1981          1 02-APR-1981
01-MAY-1981 02-APR-1981          2 03-APR-1981
01-MAY-1981 02-APR-1981          3 04-APR-1981
01-MAY-1981 02-APR-1981          4 05-APR-1981
01-MAY-1981 02-APR-1981          5 06-APR-1981
01-MAY-1981 02-APR-1981          6 07-APR-1981
01-MAY-1981 02-APR-1981          7 08-APR-1981
01-MAY-1981 02-APR-1981          8 09-APR-1981
01-MAY-1981 02-APR-1981          9 10-APR-1981
01-MAY-1981 02-APR-1981         10 11-APR-1981
01-MAY-1981 02-APR-1981         11 12-APR-1981
01-MAY-1981 02-APR-1981         12 13-APR-1981
01-MAY-1981 02-APR-1981         13 14-APR-1981
01-MAY-1981 02-APR-1981         14 15-APR-1981
01-MAY-1981 02-APR-1981         15 16-APR-1981
01-MAY-1981 02-APR-1981         16 17-APR-1981
01-MAY-1981 02-APR-1981         17 18-APR-1981
01-MAY-1981 02-APR-1981         18 19-APR-1981
01-MAY-1981 02-APR-1981         19 20-APR-1981
01-MAY-1981 02-APR-1981         20 21-APR-1981
01-MAY-1981 02-APR-1981         21 22-APR-1981
01-MAY-1981 02-APR-1981         22 23-APR-1981
01-MAY-1981 02-APR-1981         23 24-APR-1981
01-MAY-1981 02-APR-1981         24 25-APR-1981
01-MAY-1981 02-APR-1981         25 26-APR-1981
01-MAY-1981 02-APR-1981         26 27-APR-1981
01-MAY-1981 02-APR-1981         27 28-APR-1981
01-MAY-1981 02-APR-1981         28 29-APR-1981
01-MAY-1981 02-APR-1981         29 30-APR-1981
01-MAY-1981 02-APR-1981         30 01-MAY-1981

如果仔细查看 WHERE 子句的话，我们会注意到 BLAKE_HD 和 JONES_HD 相减后又加上了 1，这是为了生成所需的 30 行记录（否则就是 29 行）。我们也要注意到外层查询的 SELECT 列表里 T500.ID 减去了 1，这是因为 ID 列的起始值是 1，如果在 JONES_HD 基础上加上 1 就等同于从最终结果里排除掉了 JONES_HD。
一旦生成了所需数目的行记录，接着使用 CASE 表达式来标记每一个日期是工作日或者周末（若是工作日返回 1，周末则返回 0）。最后使用聚合函数 SUM 来合计 1 的个数，并得到最终答案。