mysql 跨年按周分组

最新推荐文章于 2024-09-27 20:43:28 发布

ysksolution

最新推荐文章于 2024-09-27 20:43:28 发布

阅读量67

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_37759590/article/details/142490468

版权

mysql 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

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);