mysql根据日期累加数量_sql日期截取以及数量累加

创建表+插入测试数据

CREATE TABLE `post` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`title` varchar(30) NOT NULL,

`status` tinyint(1) NOT NULL,

`cdate` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

insert into post (title,status,cdate)values('测试1',1,'2019-08-01 10:10:10');

insert into post (title,status,cdate)values('测试1',1,'2019-08-02 11:10:20');

insert into post (title,status,cdate)values('测试1',1,'2019-08-01 12:10:20');

insert into post (title,status,cdate)values('测试1',1,'2019-08-03 13:10:20');

insert into post (title,status,cdate)values('测试1',1,'2019-08-02 14:10:20');

insert into post (title,status,cdate)values('测试1',1,'2019-08-03 15:10:20');

insert into post (title,status,cdate)values('测试1',1,'2019-08-03 16:10:20');

sql练习

获取某个日期的2019-08-03

select * from post where left(cdate,10) ='2019-08-03'; //截取

select * from post where substring(cdate,1,10) ='2019-08-03';//截取

select * from post where DATE_FORMAT(cdate, '%Y-%m-%d')='2019-08-03';//格式化时间

//按照日期分组

select count(*),substring(cdate,1,10) from post group by substring(cdate,1,10)

order by count(*) desc;

//按照日期分组,优化下

select count(*) as count,substring(cdate,1,10) as cdate from post group by

substring(cdate,1,10) order by count desc;

//累计之间的数量

set @tmptotal := 0;select cdate,(@tmptotal :=@tmptotal+count) as total

from (select count(*) as count,substring(cdate,1,10) as cdate from post

group by substring(cdate,1,10) order by cdate) as tmpdatas group by cdate;

//累计之间的数量，大于10的日期

set @tmptotal := 0;select cdate,(@tmptotal :=@tmptotal+count) as total

from (select count(*) as count,substring(cdate,1,10) as cdate from post

group by substring(cdate,1,10) order by cdate) as tmpdatas group by cdate

having total > 10;

分类:

数据库

/

浏览量:  741

2019-8-3 17:30 Saturday

昵称

邮件地址 (选填)

个人主页 (选填)

评论内容

发表评论

send