实例:课程学习表
课程学习记录的时长存储格式为时分秒"01:02:33",现需要对课程学习进行分组统计并合并计算学习时长
初始化表数据
-- ----------------------------
-- Table structure for `course_study`
-- ----------------------------
DROP TABLE IF EXISTS `course_study`;
CREATE TABLE `course_study` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '流水号',
`course_id` varchar(48) CHARACTER SET utf8 DEFAULT NULL COMMENT '课程编号',
`total_time` varchar(24) DEFAULT NULL COMMENT '累计学习时长'
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4;
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (1, 'hguochen4779', '00:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (2, 'hguochen4780', '00:08:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (3, 'hguochen4781', '00:50:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (4, 'hguochen4785', '01:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (5, 'hguochen4786', '02:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (6, 'hguochen4822', '01:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (7, 'hguochen4823', '02:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (8, 'hguochen4824', '01:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (9, 'hguochen4825', '00:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (10, 'hguochen4826', '03:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (11, 'hguochen4832', '05:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (12, 'hguochen4833', '03:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (13, 'hguochen4838', '00:40:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (14, 'hguochen4839', '01:30:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (15, 'hguochen4840', '00:04:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (16, 'hguochen4841', '00:08:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (17, 'hguochen4845', '00:50:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (18, 'hguochen4867', '00:20:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (19, 'hguochen4868', '02:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (20, 'hguochen4869', '01:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (21, 'hguochen4870', '09:00:33.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (65, 'hguochen4962', '00:00:16.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (66, 'wk0001-1', '00:00:37.09');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (71, 'hguochen4962', '00:00:18.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (72, 'hguochen4976', '00:17:52.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (73, 'hguochen4980', '00:05:40.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (74, 'wk0001-1', '00:44:0.24');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (75, 'hguochen5026', '00:00:11.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (76, 'wk0001-1', '00:00:40.34');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (77, 'hguochen5067', '00:00:30.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (78, 'hguochen5113', '00:00:10.0');
INSERT INTO `course_study` (`id`, `course_id`, `total_time`) VALUES (79, 'hguochen5122', '00:00:58.0');
处理过程如下:
1> 解析原数据中的时、分、秒
2> 分组统计课程,并求和计算次数、时长(时分秒)
3> 重新换算时长(时分秒)的格式
SELECT (SELECT name FROM course_info WHERE id = course_id) courseName,
course_num num,
CONCAT(hour_v + ((minute_v + (second_v DIV 60)) DIV 60),
':',
(minute_v + (second_v DIV 60)) MOD 60,
':',
second_v MOD 60) num1
FROM (SELECT course_id,
count(0) course_num, SUM(hour_v) hour_v,
SUM(minute_v) minute_v,
SUM(second_v) second_v
FROM (SELECT course_id,
HOUR(total_time) hour_v,
MINUTE(total_time) minute_v,
SECOND(total_time) second_v
FROM course_study) t
GROUP BY course_id) f