MySQL行转列

痛定思痛。 2024-04-17 21:56 46阅读 0赞

## 一、行转列实例 ##

## 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()) 生成列 ##

在mysql中if()函数的用法类似于java中的三元表达式，其用处也比较多，具体语法如下：

> IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false， 则返回expr3的值。

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 "语文"的方式来实现 ##

当然max（）换成sum（）也是可以大胆地

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、如果在行转列的时候不加聚合函数max()，sum() ##

**1.case when 方式**

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

结果如下：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70]  
**2. if() 方式**

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

结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70 1]

## 为什么要必须加上聚合函数呢？ ##

[Mysql 实现行转列时为什么要用max()或者其他聚合函数][Mysql _max]

## 5、分析 ##

原有的数据是这样的：

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/90ba5a4d488a407bae65ff106461a950.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/5cd622518151424491481e373932263a.png
[Mysql _max]: https://blog.csdn.net/weixin_42740530/article/details/100188188