MySQL 中行转列的方法
作者：AsiaYe 发布时间：2024-01-25 23:55:59
标签：MySQL,行,列
MySQL行转列操作
所谓的行转列操作，就是将一个表的行信息转化为列信息，说着可能比较笼统，这里先举个例子，如下：
+----+-----------+--------+-------+
| 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操作，也就是条件操作，关于这个条件语句，首先我们给出解释：
case colume
when condition1 then result1
when condition2 then result2
when condition3 then result3
else result4
end
上面的语法，可以理解为当column的值符合condition1的时候，用result1去替换column的值，以此类推，当column值都不符合的时候，用result4去替换column的值。
现在开始试验：
首先我们创建一张表，并插入如下数据：
mysql-yeyz ::>>select * from test_tbl;
+----+-----------+--------+-------+
| ID | USER_NAME | COURSE | SCORE |
+----+-----------+--------+-------+
| | 张三 | 数学 | |
| | 张三 | 语文 | |
| | 张三 | 英语 | |
| | 李四 | 数学 | |
| | 李四 | 语文 | |
| | 李四 | 英语 | |
| | 王五 | 数学 | |
| | 王五 | 语文 | |
| | 王五 | 英语 | |
+----+-----------+--------+-------+
rows in set (0.00 sec)
根据上面case when语法，当碰到课程为'数学'的时候，我们定义一个列'数学'，并把它的score填入其中，如果碰到'语文'或者'英语'，那么把它替换为0，我们可以先笼统的写出如下SQL:
mysql-yeyz ::>>SELECT user_name ,
(CASE course WHEN '数学' THEN score ELSE END ) 数学
FROM test_tbl;
+-----------+--------+
| user_name | 数学 |
+-----------+--------+
| 张三 | |
| 张三 | |
| 张三 | |
| 李四 | |
| 李四 | |
| 李四 | |
| 王五 | |
| 王五 | |
| 王五 | |
+-----------+--------+
rows in set (0.00 sec)
我们发现上面的表只有两个列，根据SQL规则，我们可以把'语文'和'英语'的值也加入进去，一次性多写几个列，如下：
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)
这下好了，我们有了所有的记录了，已经快要达到我们的目的了，看看和最终结果的差距：
+-----------+--------+--------+--------+
| user_name | 数学 | 语文 | 英语 |
+-----------+--------+--------+--------+
| 张三 | | | |
| 李四 | | | |
| 王五 | | | |
+-----------+--------+--------+--------+
好像就剩把那些同名的学生信息都合并一下就好了，自然而然我们想到了group_by(user_name)的操作，而group_by操作需要和一些聚合函数(MAX,MIN,AVG,SUM，COUNT等)进行搭配。由于每条记录中只包含当前学科的成绩，其他学科的成绩为0，所以我们使用MAX函数和SUM函数的结果是相同的，但是不能使用AVG函数和MIN函数，这应该很好理解吧。
下面我们给出最终结果：
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方式，如下
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值也统计进去，如下：
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如下：
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函数，将所有的列都写在一起，用一个字段表示，效果如下：
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)
这种方法相当于直接针对原始表做了一个分组，也能够应付一定的应用场景。
来源：https://cloud.tencent.com/developer/article/1533688
0
投稿猜你喜欢
- 程序二：addforum.php <html> <head> <link&
- 1. 题目编写程序， 4名牌手打牌，计算机随机将52张牌（不含大小鬼）发给4名牌手，在屏幕上显示每位牌手的牌。提示：设计出3个类：Card类
- 有时候，预先不知道函数需要接受多少个实参，好在Python允许函数从调用语句中调用语句中收集任意数量的实参。在参数前加上*号。来看一个制作披
- Urllib3是一个功能强大，条理清晰，用于HTTP客户端的Python库。许多Python的原生系统已经开始使用urllib3。Urlli
需求需要检查多个端口是否开放,没有找到第三方工具和服务来快速测通,就自己用python写了个脚本.原理是通过telnet来实现的,仅支持tc- 在PC端登录公司的后台管理系统或在手机上登录某个APP时，经常会发现登录成功后，返回参数中会包含token，它的值为一段较长的字符串，而后续
- 这篇文章主要介绍了如何通过Django使用本地css/js文件,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,
- 下载：pip install apschedulerpip install django-apscheduler将 django-apsch
- 大家好，我是辣条。辣条的一个朋友最近跟我诉苦：女朋友沉迷淘宝抢购无法自拔，大晚上不睡觉都在定时抢购，真是败家。都是好朋友辣条肯定帮忙，不就是
- vue提供的了transition组件来实现组件的过渡和路由的过渡，合理使用这个组建可以让我们的页面更加的灵活，提高用户体验。概念在进入/离
- 本文实例主要实现给出任意字符串，获取字符串中某字符的位置以及出现的总次数。实现该功能代码的时候可以使用函数enumerate来将字符串分离成
- mysql中用命令行复制表结构的方法主要有一下几种: 1.只复制表结构到新表CREATE TABLE 新表 SELECT * FR
- 一、概述在后期渗透测试中，经常会遇到需要向第三方发送http请求的场景，python中的requests库可以很好的满足这一要求，Reque
- 1.resource fopen(string $filename, string $mode [,bool $us
- Python工程师面试必备25条Python知识点：1.到底什么是Python？你可以在回答中与其他技术进行对比下面是一些关键点：Pytho
- 本文实例讲述了js实现鼠标感应向下滑动隐藏菜单的方法。分享给大家供大家参考。具体实现方法如下：<html><head>
- 前言首先图片格式转换的方法有很多，但是转二进制字节流的，我搜了一下午终于在 stackoverflow上搜到了 说一下为什么要在线转这个图片
- 手残删除python补救新建文件夹，下载下面的依赖wget http://vault.centos.org/7.2.1511/o
- 使用math.modf()对一个浮点数进行拆分时经常会遇到如下情况如下import mathprint(math.modf(2.4)) #
- 本文实例讲述了PHP实现通过正则表达式替换回调的内容标签。分享给大家供大家参考。具体实现方法如下：function my_wp_plugin
