时间戳

首页 > 解决方案 > 如何根据给定的日期在数据库中自定义第 1 周、第 2 周、第 3 周、第 4 周、第 N 周？

database - 如何根据给定的日期在数据库中自定义第 1 周、第 2 周、第 3 周、第 4 周、第 N 周？

问题描述

Employee_Table

eid mydate       Late
1   01/01/2018   2
1   01/02/2018   0
1   01/03/2018   30
1   01/04/2018   4
1   01/05/2018   0
1   01/06/2018   0
1   01/07/2018   3
1   01/08/2018   0 
1   01/09/2018   3
1   01/010/2018  2
1   01/011/2018  10

Select sum(late) as lates from  Employee_Table 
where mydate between '01/01/2018' AND '01/07/2018'    
Group by eid

week1 - 36
week2 - 18
week3 - 00
week4 - 00
week5 - 00

这是我的问题。如何根据给定的日期从第 1 周到第 N 周开始查询，并且应该从周日开始。

如果第一个日期不是在星期天日期结束，那么应该在星期六结束

week 1 january 01 - 06
week 2 january 07 - 13
week 3 january 14 - 20
week 4 january 21 - 27
week 5 january 28 - 31

Select ?
 as week1 
 as week2
 as week3
 as week4
 as week5
 as weekNth

 from  Employee_Table 
where mydate between '01/01/2018' AND '01/31/2018'

标签： databasems-access

解决方案

您可以使用 MySQL 的WEEK()函数来确定当前星期。所以你的 SQL 语句将是：

SELECT WEEK(mydate), SUM(lates)
FROM Employee_Table
GROUP BY WEEK(mydate)

如果您想要您指定的确切输出，即周数的“weekX”：

SELECT CONCAT('week', WEEK(mydate)), SUM(lates)
FROM Employee_Table
GROUP BY WEEK(mydate)

推荐阅读