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;
已有 0 条评论