mysql 统计每人每年1-12月个个月的分数和总分、平均分

初始化数据

DROP TABLE IF EXISTS `test_01`;
CREATE TABLE `test_01`  (
  `id` int(0) NOT NULL,
  `user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户',
  `km` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '科目',
  `fs` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '分数',
  `time` datetime(0) NULL DEFAULT NULL COMMENT '时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `test_01` VALUES (1, '小三', '语文', '98', '2020-08-06 15:51:21');
INSERT INTO `test_01` VALUES (2, '小三', '数学', '90', '2020-07-01 15:51:25');
INSERT INTO `test_01` VALUES (3, '小三', '英语', '77', '2020-06-01 15:51:28');

sql

select 
date_format(time,'%Y') as time,
user,
(select fs from test_01 f where f.user=a.user and  date_format(f.time,'%Y-%m')=date_format(a.time,'%Y-01')) as one,
(select fs from test_01 f where f.user=a.user and  date_format(f.time,'%Y-%m')=date_format(a.time,'%Y-02')) as two,
(select fs from test_01 f where f.user=a.user and  date_format(f.time,'%Y-%m')=date_format(a.time,'%Y-03')) as three,
(select fs from test_01 f where f.user=a.user and  date_format(f.time,'%Y-%m')=date_format(a.time,'%Y-04')) as four,
(select fs from test_01 f where f.user=a.user and  date_format(f.time,'%Y-%m')=date_format(a.time,'%Y-05')) as five,
(select fs from test_01 f where f.user=a.user and  date_format(f.time,'%Y-%m')=date_format(a.time,'%Y-06')) as six,
(select fs from test_01 f where f.user=a.user and  date_format(f.time,'%Y-%m')=date_format(a.time,'%Y-07')) as seven,
(select fs from test_01 f where f.user=a.user and  date_format(f.time,'%Y-%m')=date_format(a.time,'%Y-08')) as eight,
(select fs from test_01 f where f.user=a.user and  date_format(f.time,'%Y-%m')=date_format(a.time,'%Y-09')) as nine,
(select fs from test_01 f where f.user=a.user and  date_format(f.time,'%Y-%m')=date_format(a.time,'%Y-10')) as ten,
(select fs from test_01 f where f.user=a.user and  date_format(f.time,'%Y-%m')=date_format(a.time,'%Y-11')) as ele,
(select fs from test_01 f where f.user=a.user and  date_format(f.time,'%Y-%m')=date_format(a.time,'%Y-12')) as twe,
sum(fs) as su,
avg(fs) as av 
from test_01 a
GROUP BY a.user,date_format(time,'%Y')
order by time desc