mysql 跨年按周分组

最新推荐文章于 2022-11-25 14:33:11 发布

翼海同行

最新推荐文章于 2022-11-25 14:33:11 发布

阅读量333

点赞数

CC 4.0 BY-SA版权

分类专栏： jshoper开源项目 Mysql 文章标签：数据库

本文链接：https://blog.youkuaiyun.com/sdywcd/article/details/84469922

jshoper开源项目同时被 2 个专栏收录

34 篇文章

订阅专栏

Mysql

8 篇文章

订阅专栏

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

SELECT * FROM employees.sales;

select week(date),sum(cost)from sales group by week(date);

select floor(datediff(date,'1900-01-01')/7) as a,sum(cost) from sales group by floor(datediff(date,'1900-01-01')/7);

/*最终解决方案*/
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,sum(cost) from sales
group by floor(datediff(date,'1900-01-01')/7);