mysql 周跨年_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);

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