MySQL的行转列与列转行

最新推荐文章于 2024-12-05 22:36:45 发布

¥程序猿¥

最新推荐文章于 2024-12-05 22:36:45 发布

阅读量1.4k

点赞数 3

文章标签： mysql hadoop

本文链接：https://blog.csdn.net/zhao_guo_feng/article/details/110951392

版权

行转列与列转行的概念

这里需要重申一下行转列和列转行的区别。有很多的贴子在介绍的时候没有严格的区分，命名介绍的是列转行该如何操作，但是帖子的标题确写的是行转列。自始至终都没有提过列转行的事情。所以，我觉的这里应该有必要做一次区别和认识。

什么是行转列

所谓的行转列是指把数据表中具有相同key值的多行value数据，转换为使用一个key值的多列数据，使每一行数据中，一个key对应多个value。

行转列完成后，在视觉上的效果就是：表中的总行数减少了，但是列数增加了。

如下所示的转换过程就是一个简单的行转列的过程：
在这里插入图片描述

什么是列转行

所谓的列转行是指把表中同一个key值对应的多个value列，转换为多行数据，使每一行数据中，保证一个key只对应一个value。

列转行完成之后，在视觉上的效果就是：表中的列数减少了，但是行数增加了。

如下所示的转换过程就是一个简单的列转行过程：
在这里插入图片描述

行转列实验示例

在进行实验之前，我们需要先准备好我们的实验环境，准备好表和表中的初始化数据。

准备初始化表结构

CREATE TABLE `student_x` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `class` varchar(255) DEFAULT NULL,
  `score` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

准备初始化表中的数据

INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (1, '张三', '数学', 78);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (2, '张三', '英语', 93);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (3, '张三', '语文', 65);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (4, '李四', '数学', 87);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (5, '李四', '英语', 90);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (6, '李四', '语文', 76);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (7, '李四', '历史', 69);

进行行转列的实验环境最后如下所示：

示例：使用聚合函数

我们在这个示例中，使用聚合函数sum、max、min、avg来完成我们的行转列的需求，具体的实验如下。

下面是我们需要完成行转列的效果图：
使用case when语句来拼装新的数据列

select name,
	case when class = '数学' then score else null end as math_score,
	case when class = '英语' then score else null end as engilsh_score,
	case when class = '语文' then score else null end as chinese_score,
	case when class = '历史' then score else null end as history_score
from student_x;

在这里插入图片描述

基于上面的效果图，我们需要把数据结果，按照name列进行聚合，让姓名相同的数据行合并为同一行来展示，同时，每一列的科目中，只有一行数据是有成绩的，其他行成绩都是空null，所以他们结合使用max函数，可以达到合并行，并且每列的科目成绩不会因为合并行而影响到最后的每一科目的成绩。实现上述转换的SQL语句如下：

select name,
	max(case when class = '数学' then score else null end) as math_score,
	max(case when class = '英语' then score else null end) as engilsh_score,
	max(case when class = '语文' then score else null end) as chinese_score,
	max(case when class = '历史' then score else null end) as history_score
from student_x
group by name;

在这里插入图片描述

我们上面使用了case when语句来判断，其实if语句也可以达到case when语句的效果。如下是使用if语句的结果：

select name,
	max(if(class = '数学', score, null)) as math_score,
	max(if(class = '英语', score, null)) as engilsh_score,
	max(if(class = '语文', score, null)) as chinese_score,
	max(if(class = '历史', score, null)) as history_score
from student_x
group by name;

在这里插入图片描述

实现过程分析

这里我们使用了聚合函数max，把每一个学生的姓名作为key，进行分组统计。
因为每一个学生对应每一门科目的成绩只有一行记录，所以我们使用聚合函数sum统计后的每一科目的成绩，仍然是该科目单独的成绩。
如果这里每一个学生对应每一门科目有多个成绩记录，这里就不能使用聚合函数max了，如果使用max，最后的结果将是每一个学生对应每一门科目成绩的最大值。
这里之所以使用max的目的是为了达到一个分组的效果。这里的max可以使用sum、min、avg等聚合函数替换掉，它们三个的效果和sum函数的效果在这里是一样的。

总结：上面的这样的实现方式，使大家经常使用的，也是大家最熟悉的一种方式。但是这样的写法有一个问题，就是当我们的科目名称变动或者增加或者减少的时候，我们SQL语句也需要作出对应的修改。因为我们在SQL语句中已经使用了hard code硬编码的方式把科目的名称给写死了，所以这样的SQL不太灵活。

我们可以参考使用下面的几种写法，每一种写法稍微有点不同，但是这些方式基本都能满足我们的需求。

列转行实验示例

在进行列转行的实验之前，我们需要先准备好我们的实验环境，准备好表和表中的初始化数据。表结构和初始化数据如下：

准备初始化表结构

CREATE TABLE `student_y` (
  `id` int(11) DEFAULT NULL, 
  `name` varchar(255) DEFAULT NULL,
  `math_score` bigint(255) DEFAULT NULL,
  `engilsh_score` bigint(255) DEFAULT NULL,
  `chinese_score` bigint(255) DEFAULT NULL,
  `history_score` bigint(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

准备初始化表中的数据

INSERT INTO `student_y`(`id`, `name`, `math_score`, `engilsh_score`, `chinese_score`, `history_score`) VALUES (1, '张三', 78, 93, 65, NULL);
INSERT INTO `student_y`(`id`, `name`, `math_score`, `engilsh_score`, `chinese_score`, `history_score`) VALUES (2, '李四', 87, 90, 76, 69);

进行列转行的实验环境最后如下所示：

示例：使用union all功能

实验环境准备后之后，接下来我们开始使用union all的功能来实现列转行的功能。

下面是我们需要完成行转列的效果图：
我们可以对所有学生的每一个科目的成绩进行单独查询，结果如下：

select name, math_score as score from student_y;
select name, engilsh_score as score from student_y;
select name, chinese_score as score from student_y;
select name, history_score as score from student_y;

在这里插入图片描述

基于上面的每一个查询结果，把最后的结果使用union all关键词合并在一起，效果如下：

select name, math_score as score from student_y
union all
select name, engilsh_score as score from student_y
union all
select name, chinese_score as score from student_y
union all
select name, history_score as score from student_y;

在这里插入图片描述

此时，我们发现结果中已经大概实现了列转为行的需求。但是顺序没有达到要求，每个人的各个科目的成绩应该挨着，但是目前是没有挨着的。所以我们需要在基于上面的查询结果，外面在包裹一层查询，增加一个order by语句在外层查询中，即可得到我们想要的顺序。如下所示：

select * from (
	select name, math_score as score from student_y
	union all
	select name, engilsh_score as score from student_y
	union all
	select name, chinese_score as score from student_y
	union all
	select name, history_score as score from student_y
) as x order by name;

在这里插入图片描述

此时的结果已经很接近我们的最后想要的结果了，但是我们发现，每个学生的成绩我们不能区分各个科目的成绩是多少，所以我们需要把科目也纳入到结果集中，也就是我们在查询的时候，要把各个成绩对应的列名称也包含到查询的字段中。于是就有了如下的SQL

select * from (
	select name, 'math_score' as class, math_score as score from student_y
	union all
	select name, 'engilsh_score' as class, engilsh_score as score from student_y
	union all
	select name, 'chinese_score' as class, chinese_score as score from student_y
	union all
	select name, 'history_score' as class, history_score as score from student_y
) as x order by name,class;

在这里插入图片描述
总结：这里我们采用了union all的功能，把所有学生每个科目的成绩单独查询出来，然后把结果集继续合并。最后达到我们想要的列转为行的效果。但是我们发现此时的SQL语句中，和前面我们在进行行转列的时候，使用聚合函数的方式来实现行转列的方式类似，使用了hard code的硬编码，如果科目名称或数目发生改变，我们的SQL语句也需要跟着动态的去修改，这是这种方式的缺陷。但是优点就是比较容易理解。

最后总结

我们在文章的开始，首选针对行和列相互转行的概念做了简单澄清，因为有很多人会把行转列和列转行给混淆。行转列和列转行的最明显的区分如下：

行转列，最后的结果中行变少了，列变多了。
列转行，最后的结果中列变少了，行变多了。
紧接着，我们分别针对行转列和列转行进行了实验演示。