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

Question

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' 

Answer 1

您可以使用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)

Answer 2

周定义默认从周日开始，因此您可以通过检查来检查是否有不同的配置

select @@DATEFIRST

如果返回7，则一周的第一天是星期日可以通过在SQL Server 上使用以下FIRSTDATE命令来更改这一点

SET DATEFIRST

也许您可以在SQL Server上使用以下解决方案，其中我使用CTE表达式(我不确定MySQL是否支持CTE(和SQL数字表

declare @d1 date = '01/01/2018'
declare @d2 date = '12/31/2018'
;with cte as (
select datepart(wk, @d2) as n
), agg as (
Select
eid,
datepart(wk, mydate) as dp, 
sum(late) as lates 
from Employee_Table
where 
mydate between @d1 AND @d2
Group by 
eid,
datepart(wk, mydate)
)
Select 
i, eid, lates
from dbo.NumbersTable(1,datepart(wk, @d2),1) as nt
left join agg
on i = dp
order by i, eid