[[388028]]

本文轉載自微信公眾號「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.

代碼解析

上面的代碼估計很多讀者看的有點懵，這里我們將代碼先拆解開，看下每個函數里面的結果什么，大家就知道了。

首先是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：可以的，只需要構建一張臨時表，表結構也只需要一列，就是一列自增長的連續整數即可