WEEK()
SELECT mnd AS 'StartDate',
mxd AS 'EndDate',
ROW_NUMBER() OVER (PARTITION BY YEAR(mxd) ORDER BY mnd) AS 'WeekNo'
FROM
(SELECT MIN(dt) AS mnd,MAX(dt) AS mxd, nom FROM
(SELECT dt, num , CEIL(num/7) AS nom FROM
(SELECT DATE_FORMAT(CONCAT_WS('-',yr,mnt,dy),'%Y-%m-%d') AS dt,
ROW_NUMBER() OVER (ORDER BY dt) AS num
FROM
/*Query to generate date in MariaDB*/
(SELECT LPAD(seq,2,0) AS dy FROM seq_1_to_31) a,
(SELECT LPAD(seq,2,0) AS mnt FROM seq_1_to_12) b,
(SELECT seq AS yr FROM seq_2018_to_2023) c
/*-------------------------*/
HAVING dt IS NOT NULL ORDER BY dt) V ) W
GROUP BY nom) Q;
Demo fiddle here
我做的
ROW_NUMBER()
首先在子查询中,然后使用该结果除以7(一周7天)和ceiling函数(
CEIL()
). 然后执行另一个查询,以按天花板函数的结果分组
MIN()
和
MAX()
在过去的日子里。我又做了一个
函数覆盖查询
PARTITION
按年份
EndDate
