mysql之日期函数练习

最新推荐文章于 2023-12-08 12:23:23 发布

山海自有归期

最新推荐文章于 2023-12-08 12:23:23 发布

阅读量592

点赞数

分类专栏：数据库文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_50691399/article/details/121873665

版权

数据库专栏收录该内容

34 篇文章 0 订阅

订阅专栏

建表语句

create table test(`datetime` varchar(255) ,`sum` varchar(255))ENGINE=innodb CHARSET=utf8;

数据

insert into test(datetime,sum) values('2018-6-1','10');
insert into test(datetime,sum) values('2018-6-2','11');
insert into test(datetime,sum) values('2018-6-3','11');
insert into test(datetime,sum) values('2018-6-4','12');
insert into test(datetime,sum) values('2018-6-5','14');
insert into test(datetime,sum) values('2018-6-6','15');
insert into test(datetime,sum) values('2018-6-7','13');
insert into test(datetime,sum) values('2018-6-8','37');
insert into test(datetime,sum) values('2018-6-9','18');
insert into test(datetime,sum) values('2018-6-10','19');
insert into test(datetime,sum) values('2018-6-11','10');
insert into test(datetime,sum) values('2018-6-12','11');
insert into test(datetime,sum) values('2018-6-13','11');
insert into test(datetime,sum) values('2018-6-14','12');

题目：

函数小练习求每七天的平均数

最后结果：

2018-06-01~2018-06-07 12

2018-06-08~2018-06-14 15

SELECT
	CONCAT_ws(
		'~',
		DATE_ADD(
			'2018-6-1',
			INTERVAL floor(
				datediff(datetime, '2018-6-1') / 7
			) * 7 DAY
		),
		DATE_ADD(
			'2018-6-1',
			INTERVAL floor(
				datediff(datetime, '2018-6-1') / 7
			) * 7 + 6 DAY
		)
	) AS time,
	floor(avg(sum)) AS s
FROM
	test
GROUP BY
	time;