前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL的行转列

MySQL的行转列

作者头像
AsiaYe
发布2019-11-06 16:59:19
12.9K0
发布2019-11-06 16:59:19
举报
文章被收录于专栏:DBA随笔
MySQL的行转列操作 在MySQL中,经常会遇到行转列和列转行的操作,今天来看看这种问题的解决办法,先来说说行转列。

MySQL行转列操作

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

代码语言:javascript
复制
+----+-----------+--------+-------+
| 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操作,也就是条件操作,关于这个条件语句,首先我们给出解释:

代码语言:javascript
复制
case colume 
    when condition1 then result1
    when condition2 then result2
    when condition3 then result3
else result4
end

上面的语法,可以理解为当column的值符合condition1的时候,用result1去替换column的值,以此类推,当column值都不符合的时候,用result4去替换column的值。

现在开始试验:

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

代码语言:javascript
复制
mysql-yeyz ::>>select * from test_tbl;
+----+-----------+--------+-------+
| ID | USER_NAME | COURSE | SCORE |
+----+-----------+--------+-------+
|   | 张三      | 数学   |     |
|   | 张三      | 语文   |     |
|   | 张三      | 英语   |     |
|   | 李四      | 数学   |     |
|   | 李四      | 语文   |     |
|   | 李四      | 英语   |     |
|   | 王五      | 数学   |     |
|   | 王五      | 语文   |     |
|   | 王五      | 英语   |     |
+----+-----------+--------+-------+
 rows in set (0.00 sec)

根据上面case when语法,当碰到课程为'数学'的时候,我们定义一个列'数学',并把它的score填入其中,如果碰到'语文'或者'英语',那么把它替换为0,我们可以先笼统的写出如下SQL:

代码语言:javascript
复制
mysql-yeyz ::>>SELECT user_name ,
(CASE course WHEN '数学' THEN score ELSE  END ) 数学  
FROM test_tbl;
+-----------+--------+
| user_name | 数学   |
+-----------+--------+
| 张三      |      |
| 张三      |       |
| 张三      |       |
| 李四      |      |
| 李四      |       |
| 李四      |       |
| 王五      |      |
| 王五      |       |
| 王五      |       |
+-----------+--------+
 rows in set (0.00 sec)

我们发现上面的表只有两个列,根据SQL规则,我们可以把'语文'和'英语'的值也加入进去,一次性多写几个列,如下:

代码语言:javascript
复制
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)

这下好了,我们有了所有的记录了,已经快要达到我们的目的了,看看和最终结果的差距:

代码语言:javascript
复制
+-----------+--------+--------+--------+
| user_name | 数学   | 语文   | 英语   |
+-----------+--------+--------+--------+
| 张三      |      |      |      |
| 李四      |      |      |      |
| 王五      |      |      |      |
+-----------+--------+--------+--------+

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

下面我们给出最终结果:

代码语言:javascript
复制
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方式,如下:

代码语言:javascript
复制
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值也统计进去,如下:

代码语言:javascript
复制
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如下:

代码语言:javascript
复制
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函数,将所有的列都写在一起,用一个字段表示,效果如下:

代码语言:javascript
复制
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)

这种方法相当于直接针对原始表做了一个分组,也能够应付一定的应用场景。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-03-21,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA随笔 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档