创建表te
CREATE TABLE `te` (
`test_id` int(11) NOT NULL AUTO_INCREMENT,
`test_time` datetime DEFAULT NULL,
PRIMARY KEY (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
向表中插入数据
insert into te values (1,'2019-05-08 16:38:51');
insert into te values (2,'2019-05-09 16:38:51');
insert into te values (3,'2019-05-09 16:38:56');
insert into te values (4,'2019-06-09 16:38:56');
insert into te values (5,'2019-07-09 16:38:56');
insert into te values (6,'2019-08-09 16:38:56');
insert into te values (7,'2019-05-08 16:38:51');
截取年和月、月和日、具体时间,并按照月份分组
select test_id,test_time,
date_format(test_time, '%Y-%m') as ym,
date_format(test_time, '%m-%d') as md,
date_format(test_time, '%H%i%s') as time
from te group by ym,test_id;
测试结果