SQL每日一题--20210312

题目

有如下一张表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

分析：

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

参考:SQL每日一题(20210312)