mysql跨年按周分组_MySQL

mysql跨年按周分组

Sql代码 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);