mysql表行列互换

1. 问题描述

查询下面数据库表数据namecoursescore张三语文88张三数学92张三物理93李四语文79李四数学99李四物理94
查询结果如下：name语文数学物理张三889293李四799994

2. 解决方法

测试数据：

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

-- ----------------------------

-- Table structure for `score`

-- ----------------------------

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (

  `name`varchar(10) DEFAULT NULL,

  `course`varchar(10) DEFAULT NULL,

  `score`int(11) DEFAULTNULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  

-- ----------------------------

-- Records of score

-- ----------------------------

INSERT INTO `score` VALUES('张三','语文', '88');

INSERT INTO `score` VALUES('张三','数学', '92');

INSERT INTO `score` VALUES('张三','物理', '93');

INSERT INTO `score` VALUES('李四','语文', '79');

INSERT INTO `score` VALUES('李四','数学', '99');

INSERT INTO `score` VALUES('李四','物理', '94');

1. 使用IF()函数

?

1

2

3

4

5

SELECT name, 

SUM(IF(course ='语文',score,0)) as "语文",

SUM(IF(course ='数学',score,0)) as "数学",

SUM(IF(course ='物理',score,0)) as "物理"

FROM score GROUP BYname;

2. 使用CASE WHEN

?

1

2

3

4

5

SELECT name, 

SUM(CASEWHEN course = '语文'THEN score ELSE0 END)as "语文",

SUM(CASEWHEN course = '数学'THEN score ELSE0 END)as "数学",

SUM(CASEWHEN course = '物理'THEN score ELSE0 END)as "物理"

FROM score GROUP BYname;