[[388028]]

本文轉載自微信公眾號「SQL數(shù)據庫開發(fā)」，作者丶平凡世界。轉載本文請聯(lián)系SQL數(shù)據庫開發(fā)公眾號。

今天微信群里有位讀者提了個問題：如何用SQL統(tǒng)計一個月里工作日的數(shù)據?

我覺得這個問題問的挺好的，因為我們工作中其實很多時候確實只需要統(tǒng)計大家工作日的數(shù)據，比如考勤，有的甚至還有排除一些法定節(jié)假日(這個問題大家可以思考一下如何解決)。

下面我們針對讀者提出的這個問題，用SQL來進行求解。

題目

求解員工“張三”工作日上了多少天班?

測試數(shù)據

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') 

分析

要求解工作日的天數(shù)，只需要排除掉周末即可，這里我們暫不考慮忘打卡的情況。我們可以借助SQL Server里面的系統(tǒng)表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.

代碼解析

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

首先是spt_values這個系統(tǒng)表，我們在之前的文章里有提到過具體的用法。

其次我們看下關聯(lián)條件：

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 

結果如下：

這樣，我們就可以通過關聯(lián)條件來獲取到我們需要的上班日期了，但是這并不是工作日的上班日期，我們最后還要做一個判斷，那就是SELECT后面的CASE WHEN條件了。

CASE WHEN里面的代碼我們也單獨執(zhí)行一下：

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里面的嵌套函數(shù)DATEADD也單獨拎出來，看下執(zhí)行結果：

圖片

這里的DATEPART的功能主要是用來返回這一天是這個星期的第幾天，我們的系統(tǒng)日歷是按照美國的歷法，每周的第一天是星期日，所以2021-03-01的星期一是本周的第二天，以此類推，我們得到每一天對應在本周的第幾天。

知道這個結果后，我們可以得知，每個周的第2-6天是對應我們的工作日，那么我們可以取這個結果IN (2,3,4,5,6) 也可以 NOT IN (1,7)。

這里我們用CASE WHEN取的反義詞，當它IN (1,7)時我們返回0，表示不統(tǒng)計，其他結果返回1，表示統(tǒng)計。

即：

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