mysql 显示某个教师在某一时间段内每隔30分钟点击某个事件的次数sql

1、建表造数

DROP TABLE IF EXISTS `m_temp`;
CREATE TABLE `m_temp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `d` datetime NOT NULL,
  `content` varchar(200) COLLATE utf8_bin DEFAULT NULL,
  `tid` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of m_temp
-- ----------------------------
BEGIN;
INSERT INTO `m_temp` VALUES (1, '2019-08-20 18:06:00', '事件1', 1);
INSERT INTO `m_temp` VALUES (2, '2019-08-20 20:05:00', '事件1', 1);
INSERT INTO `m_temp` VALUES (3, '2019-08-20 20:06:00', '事件1', 1);
INSERT INTO `m_temp` VALUES (4, '2019-08-20 20:30:00', '事件1', 2);
INSERT INTO `m_temp` VALUES (5, '2019-08-21 21:05:00', '事件1', 1);
INSERT INTO `m_temp` VALUES (6, '2019-08-21 21:06:00', '事件1', 1);
INSERT INTO `m_temp` VALUES (7, '2019-08-21 21:07:00', '事件1', 2);
INSERT INTO `m_temp` VALUES (8, '2019-08-22 21:01:00', '事件1', 2);
INSERT INTO `m_temp` VALUES (9, '2019-08-22 21:07:00', '事件1', 2);
INSERT INTO `m_temp` VALUES (10, '2019-08-21 21:06:00', '事件2', 2);
INSERT INTO `m_temp` VALUES (11, '2019-08-20 21:07:00', '事件2', 1);
INSERT INTO `m_temp` VALUES (12, '2019-08-22 21:07:00', '事件2', 2);
INSERT INTO `m_temp` VALUES (13, '2019-08-21 21:07:00', '事件2', 2);
INSERT INTO `m_temp` VALUES (14, '2019-08-22 21:06:00', '事件2', 2);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

 

 

2、sql

select count(*),tid, content, DATE_FORMAT( concat(date(d),' ',hour(d),':',floor( minute(d)/30 )*30) ,'%Y-%m-%d %H:%i')  as dataStartTime   

 from m_temp WHERE d>='2019-08-20 18:00' and d<='2019-08-22 21:30' group by tid, content, DATE_FORMAT( dataStartTime ,'%Y-%m-%d %H:%i')

 ORDER BY dataStartTime

 

3、效果

 

未排序前

 

执行效果