本文转载自微信公众号《SQL数据库开发》，作者，平凡的世界。转载本文请联系SQL数据库开发公众号。今天微信群里有个读者问了一个问题：如何用SQL统计一个月中工作日的数据？我觉得这个问题很好，因为其实很多时候在我们的工作中，真的只需要统计每个人工作日的数据，比如考勤，有的甚至排除了一些法定节假日（大家可以想想怎么解决这个问题）。下面我们使用SQL来解决读者提出的问题。问题员工“张三”工作多少天？测试数据CREATETABLETmp0317(nameVARCHAR(20),上班时间DATETIME,下班时间DATETIME)INSERTINTOTmp0317VALUE('张三','2021-03-0108:05:03','2021-03-0118:25:26')INSERTINTOTmp0317VALUE('张三','2021-03-0308:12:12','2021-03-0118:01:16')INSERTINTOTmp0317VALUE('张三','2021-03-0408:11:24','2021-03-0118:09:25')INSERTINTOTmp0317VALUE('张三','2021-03-0508:15:08','2021-03-0118:14:43')INSERTINTOTmp0317VALUE('张三','2021-03-0908:20:26','2021-03-0118:23:48')INSERTINTOTmp0317VALUE('张三','2021-03-1008:23:16','2021-03-0118:19:04')INSERTINTOTmp0317VALUE('张三','2021-03-1108:19:13','2021-03-0118:26:29')INSERTINTOTmp0317VALUE('张三','2021-03-1208:17:42','2021-03-0118:11:12')INSERTINTOTmp0317VALUE('张三','2021-03-1308:15:37','2021-03-0118:10:05')分析需要解决工作天数，只需要排除周末，这里不考虑忘记签到的情况。我们可以使用SQLServer中的系统表spt_values来解决具体的解决方法SELECTSUM(CASEWHENDATEPART(WEEKDAY,DATEADD(DD,NUMBER,'2021-03-01'))IN(1,7)THEN0ELSE1END)ASWORKDAYFROMMASTER..SPT_VALUESJOINTmp0317ONDATEADD(DAY,NUMBER,CONVERT(DATE,'2021-03-01'))=CONVERT(DATE,workinghours)WHERETYPE='P'ANDNUMBERBETWEEN0ANDDATEDIFF(DAY,'2021-03-01',DATEADD(MONTH,1,'2021-03-01'))-1（提示：可以左右滑动代码）结果是：我们可以看日历表，“张三”打开这几天工作日的记录：红色方框表示工作日，绿色方框表示张三周末一共有9条记录。13号周六，他应该回公司加班，但是我们不统计正常工作日的出勤记录，所以结果是8。.妈的，这里先把代码拆解一下，看看每个函数的运行结果，大家就知道了。首先是spt_values系统表，具体用法我们在上一篇文章中有提到。接下来看关联条件：JOINTmp0317ONDATEADD(DAY,NUMBER,CONVERT(DATE,'2021-03-01'))=CONVERT(DATE,workinghours)这里分别看两个等号的结果：SELECTDATEADD(DAY,NUMBER,CONVERT(DATE,'2021-03-01'))FROMMASTER..SPT_VALUESWHERETYPE='P'ANDNUMBERBETWEEN0ANDDATEDIFF(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天的记录没有被截取，大家可以去电脑上试试看。然后看等号右边的结果：SELECTCONVERT(DATE,workingtime)FROMTmp0317结果如下：这样我们就可以通过关联条件得到我们需要的工作日期了，但是这个不是工作日的工作日期，我们最后还要多做一个判断，那就是SELECT后面的CASEWHEN条件。我们分别执行CASEWHEN中的代码：SELECTDATEADD(DD,NUMBER,'2021-03-01'),DATEPART(WEEKDAY,DATEADD(DD,NUMBER,'2021-03-01'))ASWORKDAYFROMMASTER..SPT_VALUESJOINTmp0317ONDATEADD(DAY,NUMBER,CONVERT(DATETIME,'2021-03-01'))=CONVERT(DATE,workinghours)WHERETYPE='P'ANDNUMBERBETWEEN0ANDDATEDIFF(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)或NOTIN(1,7)。这里使用CASEWHEN的反义词，当为IN(1,7)时，返回0，表示不统计，其他结果返回1，表示统计。即：SELECTDATEADD(DD,NUMBER,'2021-03-01'),DATEPART(WEEKDAY,DATEADD(DD,NUMBER,'2021-03-01')),CASEWHENDATEPART(WEEKDAY,DATEADD(DD,NUMBER,'2021-03-01'))IN(1,7)THEN0ELSE1ENDASWORKDAYFROMMASTER..SPT_VALUESJOINTmp0317ONDATEADD(DAY,NUMBER,CONVERT(DATETIME,'2021-03-01'))=CONVERT(DATE,workinghours)WHERETYPE='P'ANDNUMBERBETWEEN0ANDDATEDIFF(DAY,'2021-03-01',DATEADD(MONTH,1,'2021-03-01'))-1结果是：对WORKDAY列求和得到我们的结果8Q：这里可以不用spt_values吗？A：可以，只需要建一个临时表，表结构只需要一列，是一个自增的连续整数
