如何知道WEEK函数所涵盖的日期间隔

Question

我想做另外两列，分别显示一周中分组的第一天和最后一天。

目前，我正在按周周期汇总一个查询。当我运行这个查询时，它会显示表上的结果：

SELECT 
pc.date,
CONCAT(YEAR(pc.date), '/', WEEK(pc.date)) as year_week  
FROM pc
GROUP BY CONCAT(YEAR(pc.date), '/', WEEK(pc.date))
ORDER BY pc.date

date	year_week
2020-09-02	2020/35
2020-09-07	2020/36
2020-09-17	2020/37
2020-09-23	2020/38
2020-09-28	2020/39
2020-10	2020/40
2020-10-11	2020/41
2020-10-21	2020/42
2020-10-28	2020/43

您可以使用WEEKDAY函数。演示：

select 
date_add(dt, interval -WEEKDAY(dt)-1 day ) FirstDayOfWeek, 
date_add(date_add(dt, interval  -WEEKDAY(dt)-1 day), interval 6 day) LastDayOfWeek,
week(dt) wk
from (
select '2020-09-02' dt union all
select '2020-09-07' union all
select '2020-09-17' 
) t

返回

FirstDayOfWeek  LastDayOfWeek   wk
2020-08-30  2020-09-05  35
2020-09-06  2020-09-12  36
2020-09-13  2020-09-19  37