MySQL 中行转列的方法

数据库 2024/11/23 佚名

3 2 1

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:52SELECT 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:42SELECT 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:06SELECT 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:29SELECT 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:13SELECT 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 行转列的资料请关注其它相关文章！

MySQL,行,MySQL,列,MySQL,行转列

标签：

MySQL,行,MySQL,列,MySQL,行转列

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

评论“MySQL 中行转列的方法”

MySQL 中行转列的方法

暂无“MySQL 中行转列的方法”评论...

www.imxmx.com 杰晶网络

8,675无损音乐

1,324高清电影

213破解软件

120,141站长资源

MySQL 中行转列的方法

MySQL,行,MySQL,列,MySQL,行转列

详解mysql中的字符集和校验规则

MySQL 字符类型大小写敏感

评论“MySQL 中行转列的方法”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

MySQL 中行转列的方法

MySQL,行,MySQL,列,MySQL,行转列

详解mysql中的字符集和校验规则

MySQL 字符类型大小写敏感

评论“MySQL 中行转列的方法”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存