MySQL行转列

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

一、行转列实例

1、准备数据

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

最终想要的结果是这样：

  1. | 姓名 | 语文  | 数学  |  物理  |
  2. +------+------+-------+--------+
  4. | 张三 | 74.00 | 83.00 | 93.00 | 
  6. | 李四 | 74.00 | 84.00 | 94.00 |

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

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

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

  1. SELECT
  2.     cname AS "姓名",
  3.     SUM(IF(cource="语文", score, 0)) AS "语文",
  4.     SUM(IF(cource="数学", score, 0)) AS "数学",
  5.     SUM(IF(cource="物理", score, 0)) AS "物理"
  6. FROM
  7.     tb
  8. GROUP BY cname

结果如下：

  1. 张三  74  83  93
  2.  李四  74  84  94

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

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

  1. SELECT
  2.     cname AS "姓名",
  3.     MAX(CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文", 
  4.     MAX(CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学", 
  5.     MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理"
  6. FROM tb 
  7. GROUP BY `cname`

结果如下：

  1. 张三  74  83  93
  2. 李四  74  84  94

4、如果在行转列的时候不加聚合函数max()，sum()

1.case when 方式

  1. SELECT
  2.         cname AS "姓名",
  3.         (CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文", 
  4.         (CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学", 
  5.         (CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理"
  6.     FROM tb 
  7.     GROUP BY `cname`

结果如下：

在这里插入图片描述
2. if() 方式

  1. SELECT
  2.         cname AS "姓名",
  3.         (IF(cource="语文", score, 0)) AS "语文",
  4.         (IF(cource="数学", score, 0)) AS "数学",
  5.         (IF(cource="物理", score, 0)) AS "物理"
  6.     FROM
  7.         tb
  8.     GROUP BY cname

结果如下：
在这里插入图片描述

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

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

5、分析

原有的数据是这样的：

  1. cname cource score
  2. 张三  语文  74
  3. 张三  数学  83
  4. 张三  物理  93
  5. 李四  语文  74
  6. 李四  数学  84
  7. 李四  物理  94

想变成这样：

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

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

  1. 张三  语文  74
  2. 李四  语文  74

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

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

  1. cname cource score gender
  2. 张三  语文  74  男
  3. 张三  数学  83  男
  4. 张三  物理  93  男
  5. 李四  语文  74  男
  6. 李四  数学  84  男
  7. 李四  物理  94  男
  8. 张三  语文  80  女
  9. 张三  数学  80  女
  10. 张三  物理  80  女

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

  1. SELECT
  2.     cname AS "姓名",
  3.     gender AS "性别",
  4.     MAX(CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文", 
  5.     MAX(CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学", 
  6.     MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理"
  7. FROM tb 
  8. GROUP BY `cname`, gender

显示结果是：

  1. 张三  女   80  80  80
  2. 张三  男   74  83  93
  3. 李四  男   74  84  94

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

  1. 张三  男   80  83  93
  2. 李四  男   74  84  94

发表评论

表情：
评论列表 （有 0 条评论，142人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MySQL行转列

    离恨恰如春草，更行更远还生 先看结果： ![这里写图片描述][70] 把上面一个字段，里面的每个数据都是用逗号分隔的，把它变成下面这样： ![这里写图片描述][

    落日映苍穹つ落日映苍穹つ/ 0 赞/ 288 阅读