如何用SQL只统计工作日的数据？

本文转载自微信公众号「SQL数据库开发」，何用作者丶平凡世界。统计转载本文请联系SQL数据库开发公众号。工作

今天微信群里有位读者提了个问题：如何用SQL统计一个月里工作日的数据数据?

我觉得这个问题问的挺好的，因为我们工作中其实很多时候确实只需要统计大家工作日的何用数据，比如考勤，统计有的工作甚至还有排除一些法定节假日(这个问题大家可以思考一下如何解决)。

下面我们针对读者提出的数据这个问题，用SQL来进行求解。何用

题目

求解员工“张三”工作日上了多少天班?统计

测试数据

CREATE TABLE Tmp0317 ( 姓名 VARCHAR(20), 上班时间 DATETIME, 下班时间 DATETIME ) INSERT INTO Tmp0317 VALUE (张三,2021-03-01 08:05:03,2021-03-01 18:25:26) INSERT INTO Tmp0317 VALUE (张三,2021-03-03 08:12:12,2021-03-01 18:01:16) INSERT INTO Tmp0317 VALUE (张三,2021-03-04 08:11:24,2021-03-01 18:09:25) INSERT INTO Tmp0317 VALUE (张三,2021-03-05 08:15:08,2021-03-01 18:14:43) INSERT INTO Tmp0317 VALUE (张三,2021-03-09 08:20:26,2021-03-01 18:23:48) INSERT INTO Tmp0317 VALUE (张三,2021-03-10 08:23:16,2021-03-01 18:19:04) INSERT INTO Tmp0317 VALUE (张三,2021-03-11 08:19:13,2021-03-01 18:26:29) INSERT INTO Tmp0317 VALUE (张三,2021-03-12 08:17:42,2021-03-01 18:11:12) INSERT INTO Tmp0317 VALUE (张三,2021-03-13 08:15:37,2021-03-01 18:10:05)

分析

要求解工作日的天数，只需要排除掉周末即可，工作这里我们暂不考虑忘打卡的数据情况。我们可以借助SQL Server里面的何用系统表spt_values来进行求解

具体解法

SELECT  SUM( CASE WHEN DATEPART(WEEKDAY,DATEADD(DD,NUMBER,2021-03-01 )) IN (1,7)  THEN 0  ELSE 1 END ) AS WORKDAY FROM MASTER..SPT_VALUES JOIN Tmp0317 ON DATEADD(DAY, NUMBER, CONVERT(DATE, 2021-03-01))=CONVERT(DATE,上班时间) WHERE TYPE=P  AND NUMBER BETWEEN 0 AND DATEDIFF(DAY, 2021-03-01, DATEADD(MONTH, 1, 2021-03-01))-1

(提示：可以左右滑动代码)

结果为：

我们可以对照日历表看下，“张三”在这几天的统计工作日打开记录：

其中红色框表示工作日，绿色框表示周末，工作张三总共9条记录，13日周六这天应该是回公司加班了，但是我们不算正常工作日的考勤记录，所以结果是8.

代码解析

上面的代码估计很多读者看的云南idc服务商有点懵，这里我们将代码先拆解开，看下每个函数里面的结果什么，大家就知道了。

首先是spt_values这个系统表，我们在之前的文章里有提到过具体的用法。

其次我们看下关联条件：

JOIN Tmp0317 ON  DATEADD(DAY, NUMBER, CONVERT(DATE, 2021-03-01))=CONVERT(DATE,上班时间) 

这里我们单独看等号两本的结果：

SELECT  DATEADD(DAY, NUMBER, CONVERT(DATE, 2021-03-01)) FROM MASTER..SPT_VALUES  WHERE TYPE=P  AND NUMBER BETWEEN 0 AND DATEDIFF(DAY, 2021-03-01, DATEADD(MONTH, 1, 2021-03-01))-1

注意：这里必须加上后面的WHERE条件部分，其中后面的

DATEDIFF(DAY, 2021-03-01, DATEADD(MONTH, 1, 2021-03-01))-1

的结果是30，这里因为我们的NUMBER是从0开始，所以后面要减去1，即从0-30，表示3月共31天

查询出的结果如下：

后面还有16-31日的记录未截取，大家可以去自己电脑上试验一下。

然后再看等号右边的结果：

SELECT CONVERT(DATE,上班时间)  FROM Tmp0317 

结果如下：

这样，我们就可以通过关联条件来获取到我们需要的上班日期了，但是这并不是工作日的源码库上班日期，我们最后还要做一个判断，那就是SELECT后面的CASE WHEN条件了。

CASE WHEN里面的代码我们也单独执行一下：

SELECT  DATEADD(DD,NUMBER,2021-03-01 ), DATEPART(WEEKDAY,DATEADD(DD,NUMBER,2021-03-01 )) AS WORKDAY FROM MASTER..SPT_VALUES JOIN Tmp0317 ON DATEADD(DAY, NUMBER, CONVERT(DATETIME, 2021-03-01))=CONVERT(DATE,上班时间) WHERE TYPE=P AND NUMBER BETWEEN 0 AND DATEDIFF(DAY, 2021-03-01, DATEADD(MONTH, 1, 2021-03-01))-1

我们将DATEPART里面的嵌套函数DATEADD也单独拎出来，看下执行结果：

图片

这里的DATEPART的功能主要是用来返回这一天是这个星期的第几天，我们的系统日历是按照美国的历法，每周的第一天是星期日，所以2021-03-01的星期一是本周的第二天，以此类推，我们得到每一天对应在本周的第几天。

知道这个结果后，我们可以得知，每个周的第2-6天是对应我们的亿华云工作日，那么我们可以取这个结果IN (2,3,4,5,6) 也可以 NOT IN (1,7)。

这里我们用CASE WHEN取的反义词，当它IN (1,7)时我们返回0，表示不统计，其他结果返回1，表示统计。

即：

SELECT  DATEADD(DD,NUMBER,2021-03-01 ), DATEPART(WEEKDAY,DATEADD(DD,NUMBER,2021-03-01 )), CASE WHEN DATEPART(WEEKDAY,DATEADD(DD,NUMBER,2021-03-01 )) IN (1,7) THEN 0  ELSE 1 END AS WORKDAY FROM MASTER..SPT_VALUES JOIN Tmp0317 ON DATEADD(DAY, NUMBER, CONVERT(DATETIME, 2021-03-01))=CONVERT(DATE,上班时间) WHERE TYPE=P AND NUMBER BETWEEN 0 AND DATEDIFF(DAY, 2021-03-01, DATEADD(MONTH, 1, 2021-03-01))-1

结果为：

对WORKDAY列进行SUM求和就得到了我们的结果8

Q：这里能不使用spt_values吗?

A：可以的，只需要构建一张临时表，表结构也只需要一列，就是一列自增长的连续整数即可

很赞哦!（291）