mysql 跨年按周分组

use employees;
create table sales
(
    id   int auto_increment,
    date datetime     not null,
    cost int unsigned not null,
    primary key (id)
);

insert into sales (date, cost) values ('2010-12-31', 100);
insert into sales (date, cost) values ('2011-01-01', 200);
insert into sales (date, cost) values ('2011-01-02', 100);
insert into sales (date, cost) values ('2011-01-06', 100);
insert into sales (date, cost) values ('2011-01-10', 100);
insert into sales (date, cost) values ('2024-12-29', 100);
insert into sales (date, cost) values ('2024-12-30', 100);
insert into sales (date, cost) values ('2025-01-02', 100);
insert into sales (date, cost) values ('2025-01-10', 100);


/*最终解决方案*/
select date_add('1900-01-01', interval floor(datediff(`date`, '1900-01-01') / 7) * 7 day)     as week_start,
       date_add('1900-01-01', interval floor(datediff(`date`, '1900-01-01') / 7) * 7 + 6 day) as week_end,
       max(`date`) date,
       sum(cost)
from sales
group by date_add('1900-01-01', interval floor(datediff(`date`, '1900-01-01') / 7) * 7 day),
         date_add('1900-01-01', interval floor(datediff(`date`, '1900-01-01') / 7) * 7 + 6 day);

转载：https://blog.youkuaiyun.com/sdywcd/article/details/84469922