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

到此，相信大家對“用SQL只統計工作日數據的方法步驟”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！