mysql跨年按周分组_MySQL
程序员文章站
2022-05-28 15:17:16
...
bitsCN.com
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);
bitsCN.com
推荐阅读
-
MySQL 按指定字段自定义列表排序的实现
-
mysql列转行以及年月分组实例
-
mysql 按中文字段排序
-
mysql下按某一字段分组取最大(小)值所在行的数据的技巧分享
-
MySQL单表查询操作实例详解【语法、约束、分组、聚合、过滤、排序等】
-
MySQL group by对单字分组序和多字段分组的方法讲解
-
tp框架如何处理mysql先排序在分组
-
MySQL按常规排序、自定义排序和按中文拼音字母排序的方法
-
MySQL数据库Group by分组之后再统计数目Count(*)与不分组直接统计数目的区别
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated