mysql跨年按周分组

页面导航:首页 > 数据库 > Mysql > mysql跨年按周分组

mysql跨年按周分组

来源: 作者: 时间:2016-02-17 10:07 【

mysql跨年按周分组Sql代码 use employees; create table sales( id int auto_increment, date datetime not null, cost int unsigned not null, ...
跨年按周分组
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);  

 

 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<