您的位置:首页 > 数据库 > > 正文

mysql怎么把行变列(MySQL 中行转列的方法)

更多 时间:2022-01-25 00:19:39 类别:数据库 浏览量:1485

mysql怎么把行变列

MySQL 中行转列的方法

MySQL行转列操作

    所谓的行转列操作,就是将一个表的行信息转化为列信息,说着可能比较笼统,这里先举个例子,如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • +----+-----------+--------+-------+
  • | ID | USER_NAME | COURSE | SCORE |
  • +----+-----------+--------+-------+
  • |  | 张三   | 数学  |   |
  • |  | 张三   | 语文  |   |
  • |  | 张三   | 英语  |   |
  • |  | 李四   | 数学  |   |
  • |  | 李四   | 语文  |   |
  • |  | 李四   | 英语  |   |
  • |  | 王五   | 数学  |   |
  • |  | 王五   | 语文  |   |
  • |  | 王五   | 英语  |   |
  • +----+-----------+--------+-------+
  •  rows in set (0.00 sec)
  •  
  • +-----------+--------+--------+--------+
  • | user_name | 数学  | 语文  | 英语  |
  • +-----------+--------+--------+--------+
  • | 张三   |   |   |   |
  • | 李四   |   |   |   |
  • | 王五   |   |   |   |
  • +-----------+--------+--------+--------+
  •  rows in set (0.00 sec)
  •     上面的例子中,表1给出了三个学生的三门成绩,而表2是将表1的行记录信息(学科、姓名)转化为列信息,并根据不同的user_name进行分组显示。

    1 case when操作方法

        要实现上面的功能,我们需要进行分析,首先,我们需要生成三个列,分别是数学,语文和英语,然后给每个列中的值填入对应的数据。这里需要用到mysql的case when then end操作,也就是条件操作,关于这个条件语句,首先我们给出解释:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • case colume
  •   when condition1 then result1
  •   when condition2 then result2
  •   when condition3 then result3
  • else result4
  • end
  •    上面的语法,可以理解为当column的值符合condition1的时候,用result1去替换column的值,以此类推,当column值都不符合的时候,用result4去替换column的值。

        现在开始试验:

        首先我们创建一张表,并插入如下数据:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • mysql-yeyz ::>>select * from test_tbl;
  • +----+-----------+--------+-------+
  • | ID | USER_NAME | COURSE | SCORE |
  • +----+-----------+--------+-------+
  • |  | 张三   | 数学  |   |
  • |  | 张三   | 语文  |   |
  • |  | 张三   | 英语  |   |
  • |  | 李四   | 数学  |   |
  • |  | 李四   | 语文  |   |
  • |  | 李四   | 英语  |   |
  • |  | 王五   | 数学  |   |
  • |  | 王五   | 语文  |   |
  • |  | 王五   | 英语  |   |
  • +----+-----------+--------+-------+
  •  rows in set (0.00 sec)
  •     根据上面case when语法,当碰到课程为'数学'的时候,我们定义一个列'数学',并把它的score填入其中,如果碰到'语文'或者'英语',那么把它替换为0,我们可以先笼统的写出如下SQL:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • mysql-yeyz ::>>SELECT user_name ,
  • (CASE course WHEN '数学' THEN score ELSE END ) 数学
  • FROM test_tbl;
  • +-----------+--------+
  • | user_name | 数学  |
  • +-----------+--------+
  • | 张三   |   |
  • | 张三   |    |
  • | 张三   |    |
  • | 李四   |   |
  • | 李四   |    |
  • | 李四   |    |
  • | 王五   |   |
  • | 王五   |    |
  • | 王五   |    |
  • +-----------+--------+
  •  rows in set (0.00 sec)
  •     我们发现上面的表只有两个列,根据SQL规则,我们可以把'语文'和'英语'的值也加入进去,一次性多写几个列,如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • mysql-yeyz ::>>SELECT user_name ,
  •   ->   (CASE course WHEN '数学' THEN score ELSE END ) 数学,
  •   ->   (CASE course WHEN '语文' THEN score ELSE END ) 语文,
  •   ->   (CASE course WHEN '英语' THEN score ELSE END ) 英语
  •   -> FROM test_tbl;
  • +-----------+--------+--------+--------+
  • | user_name | 数学  | 语文  | 英语  |
  • +-----------+--------+--------+--------+
  • | 张三   |   |    |    |
  • | 张三   |    |   |    |
  • | 张三   |    |    |   |
  • | 李四   |   |    |    |
  • | 李四   |    |   |    |
  • | 李四   |    |    |   |
  • | 王五   |   |    |    |
  • | 王五   |    |   |    |
  • | 王五   |    |    |   |
  • +-----------+--------+--------+--------+
  •  rows in set (0.00 sec)
  •     这下好了,我们有了所有的记录了,已经快要达到我们的目的了,看看和最终结果的差距:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • +-----------+--------+--------+--------+
  • | user_name | 数学  | 语文  | 英语  |
  • +-----------+--------+--------+--------+
  • | 张三   |   |   |   |
  • | 李四   |   |   |   |
  • | 王五   |   |   |   |
  • +-----------+--------+--------+--------+
  •     好像就剩把那些同名的学生信息都合并一下就好了,自然而然我们想到了group_by(user_name)的操作,而group_by操作需要和一些聚合函数(MAX,MIN,AVG,SUM,COUNT等)进行搭配。由于每条记录中只包含当前学科的成绩,其他学科的成绩为0,所以我们使用MAX函数和SUM函数的结果是相同的,但是不能使用AVG函数和MIN函数,这应该很好理解吧。

        下面我们给出最终结果:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • mysql-yeyz 13:55:52>>SELECT user_name ,
  •   ->   MAX(CASE course WHEN '数学' THEN score ELSE END ) 数学,
  •   ->   MAX(CASE course WHEN '语文' THEN score ELSE END ) 语文,
  •   ->   MAX(CASE course WHEN '英语' THEN score ELSE END ) 英语
  •   -> FROM test_tbl
  •   -> GROUP BY USER_NAME;
  • +-----------+--------+--------+--------+
  • | user_name | 数学  | 语文  | 英语  |
  • +-----------+--------+--------+--------+
  • | 张三   |   34 |   58 |   58 |
  • | 李四   |   45 |   87 |   45 |
  • | 王五   |   76 |   34 |   89 |
  • +-----------+--------+--------+--------+
  • 3 rows in set (0.00 sec)
  •  
  •  
  •  
  • mysql-yeyz ::>>SELECT user_name ,
  •   ->   sum(CASE course WHEN '数学' THEN score ELSE END ) 数学,
  •   ->   sum(CASE course WHEN '语文' THEN score ELSE END ) 语文,
  •   ->   sum(CASE course WHEN '英语' THEN score ELSE END ) 英语
  •   -> FROM test_tbl
  •   -> GROUP BY USER_NAME;
  • +-----------+--------+--------+--------+
  • | user_name | 数学  | 语文  | 英语  |
  • +-----------+--------+--------+--------+
  • | 张三   |   34 |   58 |   58 |
  • | 李四   |   45 |   87 |   45 |
  • | 王五   |   76 |   34 |   89 |
  • +-----------+--------+--------+--------+
  • 3 rows in set (0.00 sec)
  •  
  •  
  • mysql-yeyz ::>>SELECT user_name ,
  •   ->   MIN(CASE course WHEN '数学' THEN score ELSE END ) 数学,
  •   ->   MIN(CASE course WHEN '语文' THEN score ELSE END ) 语文,
  •   ->   MIN(CASE course WHEN '英语' THEN score ELSE END ) 英语
  •   -> FROM test_tbl
  •   -> GROUP BY USER_NAME;
  • +-----------+--------+--------+--------+
  • | user_name | 数学  | 语文  | 英语  |
  • +-----------+--------+--------+--------+
  • | 张三   |   0 |   0 |   0 |
  • | 李四   |   0 |   0 |   0 |
  • | 王五   |   0 |   0 |   0 |
  • +-----------+--------+--------+--------+
  • 3 rows in set (0.00 sec)
  •      可以看出来,使用MAX和使用SUM的结果是一样的,但是使用MIN作为聚合函数,会导致最终输出的结果都是0,因为每次都选的是该user_name指定学科的最小的值,也就是0。这样的结果就很好理解了。

    2 if操作方法

        上面的case  when操作方法理解了,那么if的操作方法也很好理解,原理是一样的,只不过是把case when的语法转换为if方式,如下

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • mysql-yeyz 14:12:42>>SELECT user_name ,
  •   ->   MAX(if (course= '数学',score,) ) 数学,
  •   ->   MAX(if (course= '语文',score,) ) 语文,
  •   ->   MAX(if (course= '英语',score,) ) 英语
  •   -> FROM test_tbl
  •   -> GROUP BY USER_NAME;
  • +-----------+--------+--------+--------+
  • | user_name | 数学  | 语文  | 英语  |
  • +-----------+--------+--------+--------+
  • | 张三   |   34 |   58 |   58 |
  • | 李四   |   45 |   87 |   45 |
  • | 王五   |   76 |   34 |   89 |
  • +-----------+--------+--------+--------+
  • 3 rows in set (0.00 sec)
  • 3 添加total列

        当我们把基本的行转列实现之后,我们现在需要在转换之后的表上面添加一个total字段,这个字段的添加我们可以通过下面的方法,即在最开始统计的时候,就把score值也统计进去,如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • mysql-yeyz 14:18:06>>SELECT user_name ,
  •   ->   (CASE course WHEN '数学' THEN score ELSE END ) 数学,
  •   ->   (CASE course WHEN '语文' THEN score ELSE END ) 语文,
  •   ->   (CASE course WHEN '英语' THEN score ELSE END ) 英语,
  •   -> (score) total
  •   -> FROM test_tbl;
  • +-----------+--------+--------+--------+-------+
  • | user_name | 数学  | 语文  | 英语  | total |
  • +-----------+--------+--------+--------+-------+
  • | 张三   |   34 |   0 |   0 |  34 |
  • | 张三   |   0 |   58 |   0 |  58 |
  • | 张三   |   0 |   0 |   58 |  58 |
  • | 李四   |   45 |   0 |   0 |  45 |
  • | 李四   |   0 |   87 |   0 |  87 |
  • | 李四   |   0 |   0 |   45 |  45 |
  • | 王五   |   76 |   0 |   0 |  76 |
  • | 王五   |   0 |   34 |   0 |  34 |
  • | 王五   |   0 |   0 |   89 |  89 |
  • +-----------+--------+--------+--------+-------+
  • 9 rows in set (0.00 sec)
  •     上面的结果是没有聚合后的结果,这里需要注意的是,如果我们要聚合,前三个列可以使用sum或者max的方法,最后一个列一定要使用sum的方法,因为我们要求的是总成绩,使用max的方法会导致取值变为分数最高的那个值。最后的sql如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • mysql-yeyz 14:18:29>>SELECT user_name ,
  •   ->   sum(CASE course WHEN '数学' THEN score ELSE END ) 数学,
  •   ->   sum(CASE course WHEN '语文' THEN score ELSE END ) 语文,
  •   ->   sum(CASE course WHEN '英语' THEN score ELSE END ) 英语,
  •   -> sum(score) total
  •   -> FROM test_tbl
  •   -> GROUP BY USER_NAME;
  • +-----------+--------+--------+--------+-------+
  • | user_name | 数学  | 语文  | 英语  | total |
  • +-----------+--------+--------+--------+-------+
  • | 张三   |   34 |   58 |   58 |  150 |
  • | 李四   |   45 |   87 |   45 |  177 |
  • | 王五   |   76 |   34 |   89 |  199 |
  • +-----------+--------+--------+--------+-------+
  • 3 rows in set (0.00 sec)
  • 4 简单方法 group_concat

        如果我们对于结果的显示格式要求不是那么细致的话,也可以用一种粗犷的方法,就是group_concat函数,将所有的列都写在一起,用一个字段表示,效果如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • mysql-yeyz 14:19:13>>SELECT user_name,
  • GROUP_CONCAT(`course`,":",score)AS 成绩
  • FROM test_tbl
  • GROUP BY user_name;
  • +-----------+-------------------------------+
  • | user_name | 成绩             |
  • +-----------+-------------------------------+
  • | 张三   | 数学:34,语文:58,英语:58    |
  • | 李四   | 数学:45,语文:87,英语:45    |
  • | 王五   | 数学:76,语文:34,英语:89    |
  • +-----------+-------------------------------+
  • 3 rows in set (0.00 sec)
  •     这种方法相当于直接针对原始表做了一个分组,也能够应付一定的应用场景。

    以上就是MySQL 中行转列的方法的详细内容,更多关于MySQL 行转列的资料请关注开心学习网其它相关文章!

    原文链接:https://cloud.tencent.com/developer/article/1533688

    标签:mysql
    您可能感兴趣