3_1_根据月份进行分组统计

需求

考勤表中记录了工人每天的出勤工时、工日，想查看工人每月总出勤工时和工日

实现

SELECT YEAR(attendance_date) as attendance_year,MONTH(attendance_date) as attendance_month, 
worker_id,sum(attendance_day) total_day , sum(attendance_time) total_time
FROM `attendance`
group by YEAR(attendance_date),MONTH(attendance_date),worker_id
order by worker_id,attendance_year,attendance_month

结果

表结构

-- ----------------------------
-- Table structure for attendance
-- ----------------------------
DROP TABLE IF EXISTS `attendance`;
CREATE TABLE `attendance`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `worker_id` bigint(20) NULL DEFAULT NULL COMMENT '工人id',
  `attendance_date` date NULL DEFAULT NULL COMMENT '上班日期',
  `attendance_time` decimal(5, 1) NULL DEFAULT NULL COMMENT '工时',
  `attendance_day` decimal(5, 1) NULL DEFAULT NULL COMMENT '工日',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9507 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '考勤表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of attendance
-- ----------------------------
INSERT INTO `attendance` VALUES (1, 1, '2021-12-24', 1.2, 1.0);
INSERT INTO `attendance` VALUES (2, 2, '2021-12-24', 0.5, 1.0);
INSERT INTO `attendance` VALUES (3, 1, '2021-12-30', 0.4, 1.0);
INSERT INTO `attendance` VALUES (4, 2, '2021-12-30', 0.2, 1.0);
INSERT INTO `attendance` VALUES (5, 1, '2022-01-17', 0.6, 1.0);
INSERT INTO `attendance` VALUES (6, 2, '2022-01-19', 0.4, 1.0);
INSERT INTO `attendance` VALUES (7, 1, '2022-01-25', 0.4, 0.5);
INSERT INTO `attendance` VALUES (8, 2, '2022-01-25', 0.8, 1.0);
INSERT INTO `attendance` VALUES (9, 1, '2022-02-19', 0.1, 1.0);
INSERT INTO `attendance` VALUES (10, 3, '2022-02-21', 0.3, 0.5);
INSERT INTO `attendance` VALUES (11, 1, '2022-02-22', 0.4, 0.5);
INSERT INTO `attendance` VALUES (12, 3, '2022-02-22', 0.6, 0.5);