SQL每日一题--20210312

最新推荐文章于 2024-08-23 11:49:47 发布

原创最新推荐文章于 2024-08-23 11:49:47 发布 · 354 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#spt_values #DATEADD #MONTH #DAY

sqlserver 同时被 2 个专栏收录

11 篇文章

订阅专栏

数据库

9 篇文章

订阅专栏

本文介绍了一种使用SQL统计员工请假天数的方法，通过构造一张包含请假信息的表T0312，并利用DATEADD、DAY、MONTH等函数计算每位员工每个月的请假天数。

题目

有如下一张表T0312
在这里插入图片描述

希望得到如下结果：
在这里插入图片描述

即

测试数据

CREATE TABLE T0312
(
请假时间 DATE,
请假时间到 DATE,
姓名 VARCHAR(20)
)

INSERT INTO T0312 VALUES ('2020-5-20','2020-5-25','张三');
INSERT INTO T0312 VALUES ('2020-5-30','2020-6-1','张三');
INSERT INTO T0312 VALUES ('2020-5-24','2020-5-25','李四');
INSERT INTO T0312 VALUES ('2020-5-25','2020-6-5','李四');
INSERT INTO T0312 VALUES ('2020-4-25','2020-6-5','王五');

参考答案

官方答案:

SELECT 姓名,MONTH(请假时间) [月份]
	,SUM(DATEDIFF(DAY,请假时间,请假时间到)+1) [总天数]
FROM(SELECT 姓名
	,CASE WHEN T2.number=0 THEN T1.请假时间 ELSE DATEADD(MONTH,T2.number,T1.StartDay) END [请假时间]
	,CASE WHEN DATEADD(DAY,-1,DATEADD(MONTH,T2.number+1,T1.StartDay))<T1.请假时间到
		THEN DATEADD(DAY,-1,DATEADD(MONTH,T2.number+1,T1.StartDay)) ELSE T1.请假时间到 END  [请假时间到]	
FROM(SELECT 姓名,
请假时间,
请假时间到,
DAY(请假时间) AS "当前月第几天",
DATEADD(DAY,1-DAY(请假时间),请假时间) AS StartDay
FROM T0312) T1
INNER JOIN master..spt_values T2 ON T2.number<=DATEDIFF(MONTH,T1.请假时间,T1.请假时间到)
WHERE T2.[type]='P'
)T
GROUP BY 姓名,MONTH(请假时间)
ORDER BY 姓名,[月份]

考点: master..spt_values、DATEADD、DAY、MONTH

分析：

DATEADD(DAY,1-DAY(请假时间),请假时间) 计算出当月的开始日期
DATEADD(DAY,-1,DATEADD(MONTH,T2.number+1,T1.StartDay))<T1.请假时间到 计算请假时间到是否跨月，表达式计算当前月末时间
关于master..spt_values参考https://www.cnblogs.com/jie566/p/5056613.html

参考:SQL每日一题(20210312)