MySQL行转列实现和总结

一、行转列实例

1、准备数据

CREATE TABLE tb(`cname` VARCHAR(10),cource VARCHAR(10),score INT) ENGINE=INNODB;
 
INSERT INTO tb VALUES('张三','语文',74);
INSERT INTO tb VALUES('张三','数学',83);
INSERT INTO tb VALUES('张三','物理',93);
INSERT INTO tb VALUES('李四','语文',74);
INSERT INTO tb VALUES('李四','数学',84);
INSERT INTO tb VALUES('李四','物理',94);

最终想要的结果是这样：

| 姓名 | 语文  | 数学  |  物理  |
+------+--------+--------+----+

| 张三 | 74.00 | 83.00 | 93.00 | 

| 李四 | 74.00 | 84.00 | 94.00 |

2、利用SUM(IF()) 生成列

SELECT
    cname AS "姓名",
    SUM(IF(cource="语文", score, 0)) AS "语文",
    SUM(IF(cource="数学", score, 0)) AS "数学",
    SUM(IF(cource="物理", score, 0)) AS "物理"
FROM
    tb
GROUP BY cname

张三  74  83  93
李四  74  84  94

3，利用max(CASE ... WHEN ... THEN .. ELSE END) AS "语文"的方式来实现

SELECT
    cname AS "姓名",
    MAX(CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文", 
    MAX(CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学", 
    MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理"
FROM tb 
GROUP BY `cname`

张三  74  83  93
李四  74  84  94

4、分析

• 原有的数据是这样的：

cname cource score
张三  语文  74
张三  数学  83
张三  物理  93
李四  语文  74
李四  数学  84
李四  物理  94

• 想变成这样：

姓名    语文  数学  物理
张三  74  83  93
李四  74  84  94

暂且将原先的表称为A，之后的称为B，A想成为B，主要是讲A表中cource列中的行数据变为列，抠除行转列的语法之外，使用了GROUP BY cname进行分组，A表的业务主键是cname和cource，这是一个联合主键。

SELECT * FROM tb GROUP BY cname

张三  语文  74
李四  语文  74

• 总结一：行转列，分组(GROUP BY)的列必须是除需要行转列之外的业务主键。
  例如tb表中业务主键应该是cname和cource,但是cource需要进行行转列，所以需要按照cname分组。

这里的字段比较少，体现的不明显，可以在tb表的基础上再加一列，性别：

cname cource score gender
张三  语文  74  男
张三  数学  83  男
张三  物理  93  男
李四  语文  74  男
李四  数学  84  男
李四  物理  94  男
张三  语文  80  女
张三  数学  80  女
张三  物理  80  女

这时候业务主键是cname,cource,gender，要向进行行转列，SQL应该是这样的：

SELECT
    cname AS "姓名",
    gender AS "性别",
    MAX(CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文", 
    MAX(CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学", 
    MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理"
FROM tb 
GROUP BY `cname`, gender

• 显示结果是：

张三  女   80  80  80
张三  男   74  83  93
李四  男   74  84  94

如果还是以cname分组，结果会是这样：

张三  男   80  83  93
李四  男   74  84  94