创建表+插入测试数据

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;