行转列

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50527
Source Host           : localhost:3306
Source Database       : mydb

Target Server Type    : MYSQL
Target Server Version : 50527
File Encoding         : 65001

Date: 2020-09-17 21:52:15
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `name`   varchar(10) DEFAULT NULL,
  `course` varchar(10) DEFAULT NULL,
  `score`  int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('Jack', 'Chinese', '74');
INSERT INTO `student` VALUES ('Jack', 'Math', '83');
INSERT INTO `student` VALUES ('Jack', 'English', '93');
INSERT INTO `student` VALUES ('Tony', 'Chinese', '74');
INSERT INTO `student` VALUES ('Tony', 'Math', '84');
INSERT INTO `student` VALUES ('Tony', 'English', '94');


SELECT * FROM student;
--  +------+---------+-------+
--  | name | course  | score |
--  +------+---------+-------+
--  | Jack | Chinese |	74   |
--  | Jack | Math    |	83   |
--  | Jack | English |	93   |
--  | Tony | Chinese |	74   |
--  | Tony | Math    |	84   |
--  +------+---------+-------+

-- ----------------------------
-- 根据name统计各个course的成绩
-- case when end
-------------------------------

select `name`,
 max(case `course` when 'Chinese' then score else 0 end)`Chinese`,
 max(case `course` when 'Math'then score else 0 end)`Math`,
 max(case `course` when 'English'then score else 0 end)`English`
from `student`
group by `name`;

--  +-----+-------+----+-------+
--  |name |Chinese|Math|English|
--  +-----+-------+----+-------+
--  |Jack |	 74   | 83 |  93   |
--  |Tony |	 74   | 84 |  94   |
--  +-----+-------+----+-------+