devbox
知新_RL
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL中的面试题——行转列（列转行）手把手详解思想_mysql 列转行

作者：知新_RL | 2024-04-21 05:40:40

赞

踩

mysql 列转行

目录

1、什么是行转列，列转行 ？

1.1、概念：

1.2、行转列和列转行的思想

1.2.1、行转列的思想？

1.2.2、列转行的思想?

3、实操讲解

3.1、实现行转列 

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

3.3、实现列转行 

3.4、总结 ：列转行

4、动手练习

1、什么是行转列，列转行 ？

1.1、概念：

        在MySQL中，行转列（Pivot）和列转行（Unpivot）是用于改变数据表格布局的概念，行转列和列转行是在特定数据转换需求下使用的技术，可以帮助改变数据的呈现方式，以适应不同的分析和报告要求。

        行转列：是指将原始数据表格中的行数据转换为列数据。这种操作常用于将某一列的值作为新的列名，并将对应的值填入相应的位置。

        列转行：列转行是指将原始数据表格中的列数据转换为行数据。这种操作常用于将多个列的值转换为一列，并将原来的列名作为新的列。

  场景： 

  1. MySQL中的行转列和列转行：
  2.     此时我们可以看出，同一个人他的每一科成绩信息都作为单独的一行来进行展示，现
  3.     在我们想要将他的成绩信息展示在同一行上，这个时候，就是所谓的列转行；
  4.     反之，就是我们的行转列。
  5.           张三          语文          78
  6.           张三          数学          87
  7.           张三          英语          65
  8.           李四          语文          88
  9.           李四          数学          90
  10.   	      李四          英语          98
  11.           王五          语文          73
  12.   	      王五          数学          88
  13.   	      王五          英语          90
  14.  
  15.     其目的就是将我们的数据展示为：
  16.           name      语文          数学           英语
  17.           张三       78            87            65
  18.           李四       88            90            98
  19.           王五       73            88            90

1.2、行转列和列转行的思想

1.2.1、行转列的思想？

        把它想做为一个拆分的过程，你想一想，一行数据想要作为多行数据进行展示，不把它拆开来，那怎么行！而MySQL中具有拆分的功能有哪些：首先肯定得从基础语法中想一下，我们所学习的MySQL中就有一个分组的思想（group by)，函数方面还有一个case....when等,当什么什么满足时，进行匹配。 

  太枯燥了上图：

  1. create table test_group_by(
  2.     name varchar(30) NOT NULL COMMENT '学生姓名',
  3.     chinese DOUBLE COMMENT '语文成绩',
  4.     math DOUBLE COMMENT '数学成绩',
  5.     english DOUBLE COMMENT '英语成绩'
  6. );
  7.  
  8. insert into test_group_by values('张三',80,null,null),
  9.                                 ('张三',null,90,null),
  10.                                 ('张三',null,null,100);

此时此刻，使用聚合函数会是什么样的结果：

select name,sum(chinese) as '语文',sum(math) as '数学',sum(english) as '英语' from test_group_by group by name;

这就是我们想要的结果，那在实际的操作中，没有null这一列，该怎么搞？想象！

1.2.2、列转行的思想?

        反观，当有一个很分散的数据，想有一个合并的操作，这个时候首先就会想到MySQL中的聚合函数sum()、union、union all操作。

也是直接上图：

  1. create table test(
  2.      name varchar(30) comment '测试-姓名',
  3.      chinese double comment '测试-语文成绩',
  4.      math double comment '测试-数学成绩',
  5.      english double comment '测试-英语成绩'
  6. );
  7. insert into test value ('张三',10,80,90);
  8. update test set chinese = 70 where name = '张三';
  9. select * from test;

查看数据：

如何转换呢？思想：现在我们要将这行数据列转行，所以必须要查这张表，那么这张表数据给的   有了，但是没有我们想要的列，所以得我们自个儿生成！

  1. -- 现在我们想要学科的列和成绩的列,from从表中查询到数据，此时直接将写入字段值，实际就是在传递此时查询到的值
  2. select name,
  3.     '语文' as 'subject' ,chinese as 'score',
  4.     '数学' as 'subject' ,math as 'score',
  5.     '英语' as 'subject' ,english as 'score'
  6. from test;

展示结果：

好家伙也不是我们想要的，他把我们想要得三个并排了，所以我们现在有没有一个办法，先查询一个，最后再把他们整合起来，有！就是使用union all方法！注意：使用union all得使用需要保证字段一致

  1. -- 最后再把他们整合起来，有！就是使用union all方法！,注意：使用union all得使用需要保证字段一致
  2. select name,
  3.        '语文' as 'subject',chinese as 'score'
  4. from test
  5. union all
  6. select name,
  7.        '数学' as 'subject',math as 'score'
  8. from test
  9. union all
  10. select name,
  11.        '英语' as 'subject',english as 'score'
  12. from test;

3、实操讲解

3.1、实现行转列 

  1. /*-------------------------- MySQL中的行转列和列转行 ---------------------------------
  2. -- MySQL中的行转列
  3. -- 对于我们的stu_score这张表，我们的数据展示是以行为主，同一个人物的信息被拆分为多个行，行转列的思想就是想把行数据转换为列数据
  4.   MySQL中的行转列和列转行
  5.           张三          语文          78
  6.           张三          数学          87
  7.           张三          英语          65
  8.           李四          语文          88
  9.           李四          数学          90
  10.   	      李四          英语          98
  11.           王五          语文          73
  12.   	      王五          数学          88
  13.   	      王五          英语          90
  14. 其目的就是将我们的数据展示为：
  15.           name      语文          数学           英语
  16.           张三       78            87            65
  17.           李四       88            90            98
  18.           王五       73            88            90
  19.  
  20.   假想：要想实现上边行转列之后的表，我们先观察其表结构，其包含四个字段，分别是：姓名字段、语文成绩字段、数学成绩字段、英语成绩字段
  21.   伪列，前边介绍行转列和列转行的思想中已经提到，行转列要用到分组聚合的思想，现在我们观察原表中的字段，每行都包含了
  22.        姓名字段、各学科字段、各学科成绩字段，那现在我们把表中的每一行都追加上其他学科的成绩字段。最终我们根据名字字段来group by，
  23.        是不是就解决了问题，那这个列怎么来，想象呗！
  24.  
  25.           张三          语文          78          null (数学)           null (英语)
  26.           张三          数学          87          null (语文)           null (英语)
  27.           张三          英语          65          null (数学)           null (语文)
  28.           李四          语文          88
  29.           李四          数学          90
  30.   	      李四          英语          98
  31.           王五          语文          73
  32.   	      王五          数学          88
  33.   	      王五          英语          90
  34.   行转列的解题步骤：
  35.     1、确定分组列、转换列、数据列---确定数据中哪一个作为分组列（找同名字段，上边的也就是根据name进行分组），
  36.                                哪一个作为转换列（上表也就是将各科成绩转换为列），哪一个作为数据列
  37.     2、生成伪列---声明伪列，即保证结构的完整性
  38.     3、做分组查询---为第四步做铺垫，分组查询通常跟我们的聚合函数一起使用
  39.     4、选择合适的聚合函数---使用聚合函数，把结构中的数据正确的计算出来（上边用的就是sum求和函数）
  40.  
  41.  */

1）第一步：生成分组列（name）、确定转换列（subject）、确定数据列（score）

2）生成伪列

  1. -- 生成伪列——扫描全表。根据case when end的语法，我们通过匹配查找，来筛选我们的学科，也就是当匹配中出现了语文项，就返回语文学科的成绩
  2. select name,
  3.        case subject when '语文' then score else null end as 'chinese',
  4.        case subject when '数学' then score else null end as 'math',
  5.        case subject when '英语' then score else null end as 'english'
  6. from stu_score;
  7. -- 也可以使用if，因为判断的条件比较简单
  8. select name,
  9.        IF(subject = '语文', score, null) as 'chinese',
  10.        if(subject = '数学', score, null) as 'math',
  11.        if(subject = '英语', score, null) as 'english'
  12. from stu_score;

 结果:

3）确定分组列

4）确定聚合函数

  1. -- 第三步、第四步：前边由于生成了笛卡尔积，所以我们需要使用分组聚合，在这里就选择name作为分组列，sum来作为聚合函数,因为sum函数在进行分组求和的时候忽略NULL值
  2. -- 第一种方法
  3. select name,
  4.        sum(case subject when '语文' then score else null end) as 'chinese',
  5.        sum(case subject when '数学' then score else null end) as 'math',
  6.        sum(case subject when '英语' then score else null end) as 'english'
  7. from stu_score group by name;
  8. -- 第二种方法：通过子查询把查询到的结果作为临时表
  9. select name,
  10.        sum(语文) as '语文',
  11.        sum(数学) as '数学',
  12.        sum(英语) as '英语'
  13. from(
  14.     select name,
  15.     case subject when '语文' then score else null end as '语文',
  16.     case subject when '数学' then score else null end as '数学',
  17.     case subject when '英语' then score else null end as '英语'
  18.     from stu_score
  19.     ) temp
  20. group by name;
  21. -- 第三种方法：使用max聚合函数
  22. select name,
  23.     max(case subject when '语文' then score else null end) as '语文',
  24.     max(case subject when '数学' then score else null end) as '数学',
  25.     max(case subject when '英语' then score else null end) as '英语'
  26. from stu_score group by name;
  27.  
  28. -- 将上面的行转列的查询结果存储为学生信息的表
  29. create table stu_score_row_columns as (
  30.     select name,
  31.     max(case subject when '语文' then score else null end) as '语文',
  32.     max(case subject when '数学' then score else null end) as '数学',
  33.     max(case subject when '英语' then score else null end) as '英语'
  34. from stu_score group by name
  35. );

 结果：

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

  1. 两步法：
  2. 公式：
  3. select 分组列，
  4.       聚合函数（m1）as 列名1，
  5.       聚合函数（m2）as 列名2，
  6.       聚合函数（m3）as 列名3，
  7. from (select *,
  8.     case 转换列 when 转换列值1 then 数据列 else .... end as m1,    
  9.     case 转换列 when 转换列值2 then 数据列 else .... end as m2,
  10.     case 转换列 when 转换列值3 then 数据列 else .... end as m3
  11.     from 表名）临时表名
  12. group by 分组列；
  13.  
  14.  
  15.  
  16. 一步法：
  17. 公式：
  18. select 分组列，
  19.     聚合函数（case 转换列 when 转换列值1 then 数据列 else .... end） as 列名1,    
  20.     聚合函数（case 转换列 when 转换列值2 then 数据列 else .... end） as 列名2,
  21.     聚合函数（case 转换列 when 转换列值3 then 数据列 else .... end） as 列名3
  22.     ...
  23. from 表名
  24. group by 分组列；

3.3、实现列转行 

  创建数据表：

  1. -- 将上面的行转列的查询结果存储为学生信息的表
  2.  
  3. create table stu_score_row_columns as (
  4.     select name,
  5.     max(case subject when '语文' then score else null end) as '语文',
  6.     max(case subject when '数学' then score else null end) as '数学',
  7.     max(case subject when '英语' then score else null end) as '英语'
  8. from stu_score group by name
  9. );
  10. /*
  11. 数据展示:
  12.     name 语文 数学 英语
  13.     张三  78  87  65
  14.     李四  88  90  98
  15.     王五  73  88  90
  17. 列转行之后：思想：先转换我们第一行的数据，然后一层一层拼接使用union all来操作
  18.     张三          语文          78
  19.     张三          数学          87
  20.     张三          英语          65
  21. 分析:
  22.     1、表中的张三是通过表中的name所得到，语文作为我们的表头，可以通过'列名'获取，列所对应的值通过列名直接指定
  23.  */

   结果：

   实现列转行，根据前面介绍的思想，一个道理，也是自己生成新的列，from查出来的值，可以直接书写，及代表引用该值

  1.  -- 先获取一行的数据,其中，subject列的值始终为字符串'语文'，score列的值将与stu_score_row_columns表中的语文列的值相对应。
  2.  
  3. select name,'语文' as subject,语文 as 'score' from stu_score_row_columns
  4. union all
  5. select name,'数学' as subject,数学 as 'score' from stu_score_row_columns
  6. union all
  7. select name,'英语' as subject,英语 as 'score' from stu_score_row_columns
  8. order 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

注意：

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

4、动手练习

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

创建表，并插入数据：

  1.  create table order_tab(
  2.      order_number int comment '订单号',
  3.      product_name varchar(40) comment '产品名称',
  4.      quantity int comment '销售数量'
  5.  ) COMMENT '订单表';
  6.  
  7. insert into order_tab(order_number, product_name, quantity) values (1,'球鞋',3),
  8.                                                                    (1,'羽毛球',3),
  9.                                                                    (3,'羽毛球',2),
  10.                                                                    (2,'球鞋',6),
  11.                                                                    (4,'矿泉水',3),
  12.                                                                    (2,'苏打水',1),
  13.                                                                    (2,'矿泉水',4);
  14. -- 确定我们的行转列
  15. select order_number '订单编号',
  16.        sum(case when product_name = '球鞋' then quantity else 0 end) as '球鞋',
  17.        sum(case when product_name = '羽毛球' then quantity else 0 end) as '羽毛球',
  18.        sum(case when product_name = '矿泉水' then quantity else 0 end) as '矿泉水',
  19.        sum(case when product_name = '苏打水' then quantity else 0 end) as '苏打水'
  20. 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：

  数据准备：

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

 

  代码：

  1. -- 实现列转行（列转行使用到union all）
  2.  
  3. select city_name,'product_a_demand' as '产品名称',product_a_demand as '产品数量' from market_servey
  4. union all
  5. select city_name,'product_b_demand' as '产品名称',product_b_demand as '产品数量' from market_servey
  6. order by city_name;

  结果：

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/461330
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号