2021-03-12

SQL 每日一练(SQL每日一练)
@ 题目和答案来源于 wechat：SQL数据库开发

题目：

姓名	请假时间	请假时间到
张三	2020-05-20	2020-05-25
张三	2020-05-30	2020-06-01
李四	2020-05-24	2020-05-25
李四	2020-05-24	2020-06-05
王五	2020-04-25	2020-06-05

OUTPUT

姓名	月份	总天数
李四	5	9
李四	6	5
王五	4	6
王五	5	31
王五	6	5
张三	5	8
张三	6	1

note:

1. 注意王五：
   请假时间从4月25到6月5
   所以我们要比较
2. 张三和李四
   分情况讨论。
3. CASE STATEMENT

步骤：

STEP 1:
select 姓名，month（请假时间）AS 月份，sum（datediff（DAY，请假时间，请假时间到）+1）AS 总天数
from T301 AS T

姓名	月份	总天数
张三

Step 2：
SELECT 请假时间，请假时间到，姓名
FROM T301 T1

姓名	请假时间	请假时间到
张三	2020-05-20	2020-05-25

Step 3：
SELECT 姓名，请假时间，请假时间到，DATEADD(DAY, 1-DAY(请假时间，请假时间)) STARTDAY
FROM T301

姓名	请假时间	请假时间到	STARTDAY
张三	2020-05-20	2020-05-25	2020-05-01

DATEADD(DAY, 1-DAY(请假时间），请假时间）
DATEADD(DAY, 1-20, 2020-05-20)
=> DATEADD(DAY, -19, 2020-05-20)
=> 2020-05-01

Step 4：
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 T1

姓名	请假时间	请假时间到
张三	2020-05-20
张三	2020-06-01

–
i.e. 2020-05-20 and 2020-05-25. 当月份为0，return->2020-05-20
i.e. 2020-05-30 and 2020-06-01. 当月份为1，return -> 2020-06-01
DATEADD(MONTH, T2.NUMBER，T1.startday）
=> DATEADD(MONTH, 1，2020-05-01）
=> 2020-06-01

–
i.e. DATEADD(MONTH, 2, 2020-05-01) => 2020-07-01
DATEADD(DAY，-1，2020-07-01) => 2020-07-00

JOIN master…spt_values T2 ON T2.number <= DATEDIFF(MONTH, T1.请假时间，T1.请假时间到）where T2.TYPE = ‘p’）T
Group by 姓名，month（请假时间）
order by 1,2

i.e. DATEDIFF(MONTH, 2020-05-20, 2020-05-25)
=> 0
i.e. DATEDIFF(MONTH, 2020-05-20, 2020-06-25)
=> 1

太难了！