群里看到一个题目,如下图
感觉挺有意思,于是就写了一下这个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
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语句,得到结果如下