面试被问到 SQL查询行转列

sql脚本（Mysql数据库）

DROP TABLE IF EXISTS `table_a`;
CREATE TABLE `table_a`  (
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `subject` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` int(255) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


INSERT INTO `table_a` VALUES ('张三', '语文', 60);
INSERT INTO `table_a` VALUES ('张三', '数学', 70);
INSERT INTO `table_a` VALUES ('张三', '英语', 80);
INSERT INTO `table_a` VALUES ('李四', '语文', 90);
INSERT INTO `table_a` VALUES ('李四', '数学', 100);

表数据结构如下:

需要查询出来的结构是这样子的：

 

行转列SQL:

select a.name,
sum((case a.subject when '语文' then a.score else 0 end))china,
sum((case a.subject when '数学' then a.score else 0 end))math,
sum((case a.subject when '英语' then a.score else 0 end))english
from table_a a group by a.`name`;