如何用SQL只統(tǒng)計工作日的數(shù)據(jù)？
點(diǎn)擊關(guān)注“SQL數(shù)據(jù)庫開發(fā)”，
設(shè)為“置頂或星標(biāo)”，第一時間送達(dá)干貨
今天微信群里有位讀者提了個問題：如何用SQL統(tǒng)計一個月里工作日的數(shù)據(jù)？
我覺得這個問題問的挺好的，因?yàn)槲覀児ぷ髦衅鋵?shí)很多時候確實(shí)只需要統(tǒng)計大家工作日的數(shù)據(jù)，比如考勤，有的甚至還有排除一些法定節(jié)假日（這個問題大家可以思考一下如何解決）。
下面我們針對讀者提出的這個問題，用SQL來進(jìn)行求解。
題目
求解員工“張三”工作日上了多少天班？
測試數(shù)據(jù)
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來進(jìn)行求解
具體解法
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(提示：可以左右滑動代碼)
結(jié)果為：
我們可以對照日歷表看下，“張三”在這幾天的工作日打開記錄：
其中紅色框表示工作日，綠色框表示周末，張三總共9條記錄，13日周六這天應(yīng)該是回公司加班了，但是我們不算正常工作日的考勤記錄，所以結(jié)果是8.
代碼解析
上面的代碼估計很多讀者看的有點(diǎn)懵，這里我們將代碼先拆解開，看下每個函數(shù)里面的結(jié)果什么，大家就知道了。
首先是spt_values這個系統(tǒng)表，我們在之前的文章里有提到過具體的用法，還不會的可以翻一下以前的文章：
《巧用spt_values解決SQL中的連續(xù)日期問題》
其次我們看下關(guān)聯(lián)條件：
JOIN Tmp0317 ON
DATEADD(DAY, NUMBER, CONVERT(DATE, '2021-03-01'))=CONVERT(DATE,上班時間)這里我們單獨(dú)看等號兩本的結(jié)果：
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
的結(jié)果是30，這里因?yàn)槲覀兊腘UMBER是從0開始，所以后面要減去1，即從0-30，表示3月共31天
查詢出的結(jié)果如下：
后面還有16-31日的記錄未截取，大家可以去自己電腦上試驗(yàn)一下。
然后再看等號右邊的結(jié)果：
SELECT CONVERT(DATE,上班時間)
FROM Tmp0317結(jié)果如下：
這樣，我們就可以通過關(guān)聯(lián)條件來獲取到我們需要的上班日期了，但是這并不是工作日的上班日期，我們最后還要做一個判斷，那就是SELECT后面的CASE WHEN條件了。
CASE WHEN里面的代碼我們也單獨(dú)執(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也單獨(dú)拎出來，看下執(zhí)行結(jié)果：
這里的DATEPART的功能主要是用來返回這一天是這個星期的第幾天，我們的系統(tǒng)日歷是按照美國的歷法，每周的第一天是星期日，所以2021-03-01的星期一是本周的第二天，以此類推，我們得到每一天對應(yīng)在本周的第幾天。
知道這個結(jié)果后，我們可以得知，每個周的第2-6天是對應(yīng)我們的工作日，那么我們可以取這個結(jié)果IN (2,3,4,5,6) 也可以 NOT IN (1,7)。
這里我們用CASE WHEN取的反義詞，當(dāng)它IN (1,7)時我們返回0，表示不統(tǒng)計，其他結(jié)果返回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結(jié)果為：
對WORKDAY列進(jìn)行SUM求和就得到了我們的結(jié)果8
Q：這里能不使用spt_values嗎？
A：可以的，只需要構(gòu)建一張臨時表，表結(jié)構(gòu)也只需要一列，就是一列自增長的連續(xù)整數(shù)即可
以上就是這個問題的解答過程，感興趣的讀者可以動手拆解一下，同時對于如何排除法定節(jié)假日的問題也可以思考一下，歡迎留言討論。
最后給大家分享我寫的SQL兩件套：《SQL基礎(chǔ)知識第二版》和《SQL高級知識第二版》的PDF電子版。里面有各個語法的解釋、大量的實(shí)例講解和批注等等，非常通俗易懂，方便大家跟著一起來實(shí)操。
有需要的讀者可以下載學(xué)習(xí)，在下面的公眾號「數(shù)據(jù)前線」(非本號)后臺回復(fù)關(guān)鍵字：SQL，就行
數(shù)據(jù)前線
后臺回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群
記得幫忙點(diǎn)「贊」和「在看」↓
謝謝啦