MySQL中的经典面试题——行转列（列转行）

2023-10-01 11:43

短信预约 -IT技能 免费直播动态提醒

目录

1、简介：

1. 行转列（Pivot）：

2. 列转行（Unpivot）：

2、行转列，列转行的思想

3、实现

3.1、实现行转列

3.2、总结（行转列）实现的两种方法

3.3、实现（列转行）

3.4、总结（列转行）

1、简介：

在MySQL中，行转列（Pivot）和列转行（Unpivot）是用于改变数据表格布局的概念，行转列和列转行是在特定数据转换需求下使用的技术，可以帮助改变数据的呈现方式，以适应不同的分析和报告要求。具体的实现方法会因实际需求和查询的具体情况而有所不同。

1. 行转列（Pivot）：

行转列是指将原始数据表格中的行转换为列。这种操作常用于将某一列的值作为新的列名，并将对应的值填入相应的位置。例如，将某个学生在不同科目上的成绩从纵向布局转换为横向布局。

2. 列转行（Unpivot）：

列转行是指将原始数据表格中的列转换为行。这种操作常用于将多个列的值转换为一列，并将原来的列名作为新的列。例如，将不同科目的成绩从横向布局转换为纵向布局。

2、行转列，列转行的思想

行转列：首先对于很分散的数据来说，我们的行要想把它转换为列，这就需要我们有一个和的思想，这在我们MySQL的学习过程中，聚合函数的分组聚合就尤为重要，所以在行转列的过程中，聚合函数，group by 分组列最为重要。也就可以通过使用聚合函数和条件语句（如`CASE WHEN`）来实现。具体实现方法因查询需求而异，一般使用`GROUP BY`进行分组和聚合。

列转行：那么对于一个跟聚合的数据我们又如何把它拆散，拆散之后我们又该进行什么操作，这就要想到我们将打散了的数据聚合在一起，也就是使用到我们的UNION ALL，将多个查询的结果组合在一起，并在结果中添加一个代表原列名的新列。每个查询都需选择一个子集，代表一个原始列。

3、实现

3.1、实现行转列

1）确定我们的分组列，扫视整张表，只有我们姓名没变，所以姓名作为我们的分组列，其次我们转换列，我们需要把对应的学科成绩转换成列，依次，成绩信息就作为我们的数据列

2）生成伪列

-- 生成伪列select name,       case subject when '语文' then score else null end as 'chinese',       case subject when '数学' then score else null end as 'math',       case subject when '英语' then score else null end as 'english'from stu_score;

结果：

3）确定我们的分组列为name

4）因为我们的分组语句通常跟我们的聚合函数搭配使用

-- 通过聚合函数和分组语句：select name,max(chinese) as '语文成绩',max(math) as '数学成绩',max(english) as '英语成绩'from (select name,       case subject when '语文' then score else null end as 'chinese',       case subject when '数学' then score else null end as 'math',       case subject when '英语' then score else null end as 'english'from stu_score) tempgroup by name;

结果：

3.2、总结（行转列）实现的两种方法

两步法：公式：select 分组列，      聚合函数（m1）as 列名1，      聚合函数（m2）as 列名2，      聚合函数（m3）as 列名3，from (select *,    case 转换列 when 转换列值1 then 数据列 else .... end as m1,        case 转换列 when 转换列值2 then 数据列 else .... end as m2,    case 转换列 when 转换列值3 then 数据列 else .... end as m3    from 表名）临时表名group by 分组列；一步法：公式：select 分组列，    聚合函数（case 转换列 when 转换列值1 then 数据列 else .... end） as 列名1,        聚合函数（case 转换列 when 转换列值2 then 数据列 else .... end） as 列名2,    聚合函数（case 转换列 when 转换列值3 then 数据列 else .... end） as 列名3    ...from 表名group by 分组列；

3.3、实现（列转行）

创建数据表：

-- 将上面的行转列的查询结果存储为学生信息的表create table stu_score_row_columns as (    select name,    max(case subject when '语文' then score else null end) as '语文',    max(case subject when '数学' then score else null end) as '数学',    max(case subject when '英语' then score else null end) as '英语'from stu_score group by name);

结果：

实现列转行

 -- 先获取一行的数据,其中，subject列的值始终为字符串'语文'，score列的值将与stu_score_row_columns表中的语文列的值相对应。select name,'语文' as subject,语文 as 'score' from stu_score_row_columnsunion allselect name,'数学' as subject,数学 as 'score' from stu_score_row_columnsunion allselect name,'英语' as subject,英语 as 'score' from stu_score_row_columnsorder by name;

结果：

3.4、总结（列转行）

总结：实现列转行方法
解题步骤：
1、确定转换列，非转换列
2、生成新列
3、使用union或union all来进行合并
4、根据需要进行order by排序操作

公式：
select 非转换列，'转换列1' as 新转换列名，转换列1 as 新数据列名 from 表名
union all
select 非转换列，'转换列2' as 新转换列名，转换列2 as 新数据列名 from 表名
union all
select 非转换列，'转换列3' as 新转换列名，转换列3 as 新数据列名 from 表名
order by ....;s

注意：

新转换列名和新数据列名必须保持一致。

动手练习

行转列（Pivot）题目：
1. 给定一个订单表格，包含订单号（order_number）、产品名称（product_name）和销售数量（quantity）三个字段，将该表格行转列，以订单号作为列名，各产品名称对应的销售数量作为相应的值。

创建表，并插入数据：

 create table order_tab(     order_number int comment '订单号',     product_name varchar(40) comment '产品名称',     quantity int comment '销售数量' ) COMMENT '订单表';insert into order_tab(order_number, product_name, quantity) values (1,'球鞋',3),           (1,'羽毛球',3),           (3,'羽毛球',2),           (2,'球鞋',6),           (4,'矿泉水',3),           (2,'苏打水',1),           (2,'矿泉水',4);-- 确定我们的行转列select order_number '订单编号',       sum(case when product_name = '球鞋' then quantity else 0 end) as '球鞋',       sum(case when product_name = '羽毛球' then quantity else 0 end) as '羽毛球',       sum(case when product_name = '矿泉水' then quantity else 0 end) as '矿泉水',       sum(case when product_name = '苏打水' then quantity else 0 end) as '苏打水'from order_tab group by order_number order by order_number;

结果：

列转行（Unpivot）题目：
1. 假设有一个销售数据表格，包含年份（year）、产品A的销售量（product_a_sales）和产品B的销售量（product_b_sales）两个字段，将该表格列转行，以年份为一列，并列出每个产品和对应的销售量。
2. 给定一个市场调查数据表格，包含城市名称（city_name）、产品A的需求量（product_a_demand）和产品B的需求量（product_b_demand）两个字段，将该表格列转行，以城市名称为一列，并列出每个产品和对应的需求量。

解题2：

数据准备：

-- 实现列转行（数据准备）create table market_servey(    city_name varchar(30) comment '城市名称',    product_a_demand int comment '产品A的需求量',    product_b_demand int comment '产品B的需求量');insert into market_servey(city_name, product_a_demand, product_b_demand) values ('贵阳',20,30),                        ('毕节',30,10),                        ('遵义',15,50),                        ('铜仁',60,10),                        ('黔东南',10,16);

代码：

-- 实现列转行（列转行使用到union all）select city_name,'product_a_demand' as '产品名称',product_a_demand as '产品数量' from market_serveyunion allselect city_name,'product_b_demand' as '产品名称',product_b_demand as '产品数量' from market_serveyorder by city_name;

结果：