Mysql 行列转换

mysql 用case 

CREATE TABLE `stu` (
  `name` varchar(255) DEFAULT NULL,
  `subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `result` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO stu (name, subject, result) VALUES
('张三', '语文', '89'),
('张三', '数学', '100'),
('张三', '英语', '50'),
('李四', '语文', '39'),
('李四', '数学', '87'),
('李四', '英语', '74');

　

要求输出格式如下：

 

select  name,
     max(case when subject = '语文' then cast(result as char) end ) as 语文,
     max(case when subject = '数学' then cast(result as char) end ) as 数学,
     max(case when subject = '英语' then cast(result as char) end ) as 英语 
from stu  group by name

###max 是因为group by 用了之后，select 中需要有个聚合函数