群里看到一个题目,如下图
感觉挺有意思,于是就写了一下这个sql。最要是活用minute和date_format函数
sql如下
SELECT DATE_ADD(CONCAT(DATE_FORMAT(CreateTime,'%Y-%m-%d %H:'),FLOOR(MINUTE(CreateTime)/10),"0:00"),INTERVAL 10 MINUTE) TIME,COUNT(ID) ReqCount FROM RequestInfo WHERE CreateTime >='2014-01-01' AND CreateTime<'2014-01-02' GROUP BY DATE_ADD(CONCAT(DATE_FORMAT(CreateTime,'%Y-%m-%d %H:'),FLOOR(MINUTE(CreateTime)/10),"0:00"),INTERVAL 10 MINUTE) ORDER BY TIME
建表语句如下
CREATE TABLE `requestinfo` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `CreateTime` datetime NOT NULL, PRIMARY KEY (`ID`), KEY `CreateTime` (`CreateTime`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
适当插入数据如下
insert into `requestinfo` (`ID`, `CreateTime`) values('1','2014-01-01 00:00:01'); insert into `requestinfo` (`ID`, `CreateTime`) values('2','2014-01-01 00:00:02'); insert into `requestinfo` (`ID`, `CreateTime`) values('3','2014-01-01 00:00:03'); insert into `requestinfo` (`ID`, `CreateTime`) values('4','2014-01-01 00:10:01'); insert into `requestinfo` (`ID`, `CreateTime`) values('5','2014-01-01 00:20:02'); insert into `requestinfo` (`ID`, `CreateTime`) values('6','2014-01-01 00:35:12'); insert into `requestinfo` (`ID`, `CreateTime`) values('7','2014-01-01 00:55:59');
运行sql语句,得到结果如下