Mysql将学生分数行转列并求和

建表

CREATE TABLE `score` (
    `id` INT(5) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `obj` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `fenshu` INT(10) NULL DEFAULT '0',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=11
;

插入数据

INSERT INTO `test`.`score` (`name`, `obj`, `fenshu`) VALUES ('李四', '语文', '40');
INSERT INTO `test`.`score` (`name`, `obj`, `fenshu`) VALUES ('李四', '数学', '50');
INSERT INTO `test`.`score` (`name`, `obj`, `fenshu`) VALUES ('李四', '英语', '60');

INSERT INTO `test`.`score` (`name`, `obj`, `fenshu`) VALUES ('张三', '语文', '10');
INSERT INTO `test`.`score` (`name`, `obj`, `fenshu`) VALUES ('张三', '数学', '20');
INSERT INTO `test`.`score` (`name`, `obj`, `fenshu`) VALUES ('张三', '英语', '30');

数据为：

 

 执行行转列查询：

SELECT NAME,
SUM(case when obj = '语文' then fenshu ELSE 0 END) AS '语文',
SUM(case when obj = '数学' then fenshu ELSE 0 END) AS '数学',
SUM(case when obj = '英语' then fenshu ELSE 0 END) AS '英语',
SUM(fenshu)
FROM score
GROUP BY 
NAME

查询结果为：