欢迎来到爱书不爱输的程序猿的博客, 本博客致力于知识分享,与更多的人进行学习交流
本文收录于SQL应知应会专栏,本专栏主要用于记录对于数据库的一些学习,有基础也有进阶,有MySQL也有Oracle
行列转换 • Mysql版
- 前言
- 一、MySQL行列转换
-
- 1.数据准备操作
- 2.行转列
-
- 1.1为何进行行转列?
- 1.2 行转列有两个意思:1.表内的行转列 2.跨表的行转列
- 3.行转列的思路:行变少,列变多
-
- 3.1 如何进行行转列:增加字段,进行聚合(行变少)
- 4.行转列的实操
-
- 4.1 通用的行转列(Mysql和Oracle都能用)
-
- 4.1.1想在结果中加入学生名字
-
- 4.1.1.1加入名字的方法1:
- 4.1.1.1 加入名字的方法2:
- 4.2 私有方法的行转列(Mysql用)
-
- 4.2.1 添加名字的两种方法
- 3.列转行
-
- 3.1列转行思路:行变多 用union
- 3.2 列转行实操
-
- 3.2.1 数据准备
- 3.2.2 实操
- 小结
前言
在前一篇内容中,学习了MySQL的行列转换中的行转列,其中只讲述了在MySQL与Oracle中通欧诺个的行转列,并且进行了对应的扩展了——如果想在结果中加入学生姓名的方法,上一篇讲了其中一种方法,就是使用关联子查询。
今天这篇内容,将继续进行讲述MySQL的行列转换的后续内容,其中包括添加学生姓名的第二种方法(使用了join进行关联表),以及本文章主攻的核心内容——行列转换中的列转行。
同样的,为了大家可以更方便的一起跟着文章进行代码的操作学习,在文章中的每一块的知识点都提供了对应的数据准备,即大家可以直接复制代码在自己的电脑上进行建表,然后根据文章中的内容一起进行实操,因为我个人认为计算机方面的知识点的学习,实操相对于光进行文字的学习会更有效果,并且更容易发现自己的短板和思路中存在的问题。
那么,快拿出你的电脑,跟着文章一起学习起来吧
一、MySQL行列转换
1.数据准备操作
👉:传送门💖数据准备操作💖
2.行转列
1.1为何进行行转列?
👉:传送门💖1.1为何进行行转列?💖
1.2 行转列有两个意思:1.表内的行转列 2.跨表的行转列
👉:传送门💖1.2 行转列的两个意思💖
3.行转列的思路:行变少,列变多
3.1 如何进行行转列:增加字段,进行聚合(行变少)
👉:传送门💖3.1如何进行行转列💖
4.行转列的实操
4.1 通用的行转列(Mysql和Oracle都能用)
👉:传送门💖4.1通用的行转列(Mysql和Oracle都能用)💖
4.1.1想在结果中加入学生名字
👉:传送门💖4.1.1在结果中加入学生名字)💖
4.1.1.1加入名字的方法1:
4.1.1.1 加入名字的方法2:
select distinct t1.user_name ,t0.* -- 如果不加distinct,因为t1表每个ID对应多个名字,所以最终结过就是,名字重复几次,结果就有几行重复
from
(
select
user_id '学生ID',
max(case when course = '语文' then score end) '语文',
max(case when course = '数学' then score end) '数学',
max(case when course = '英语' then score end) '英语'
from
table_grade
group by
user_id
) t0left join table_grade t1
on t0.学生ID = t1.user_id; -- 此处的t0.学生ID,因为前面设置了别名,所以此处也应该使用别名,不然就会发生错误:Unknown column 't0.user_id' in 'on clause'
4.2 私有方法的行转列(Mysql用)
select user_id '学生ID',
max(if(course = '语文',score,null)) '语文',
max(if(course = '数学',score,null)) '数学',
max(if(course = '英语',score,null)) '英语'
from table_grade
group by user_id
4.2.1 添加名字的两种方法
select user_id '学生ID',
(select max(user_name) from table_grade where table_grade.user_id =t.user_id ) user_name,
max(if(course = '语文',score,null)) '语文',
max(if(course = '数学',score,null)) '数学',
max(if(course = '英语',score,null)) '英语'
from table_grade t
group by user_id
select distinct t1.user_name,t0.*
from
( select user_id '学生ID',
max(if(course = '语文',score,null)) '语文',
max(case when course = '数学' then score end) '数学',
max(if(course = '英语',score,null)) '英语'
from table_grade
group by user_id ) t0
left join table_grade t1
on t0.学生ID = t1.user_id;
3.列转行
a b c
1 1 a
2 1 b
3 1 c
2 a
2 b
2 c
3 a
3 b
3 c
列转行如上图所示,左边变成右边
- 右图又称为纵表,这种纵表在大数据中适合用工具hbase进行列式存储,里面存的就是键值对,右图的左列是键、右列是值
- 纵表适合存储,横表适合分析
- 底层明细数据,适合列式存储
3.1列转行思路:行变多 用union
- select(查询)能表达的关系是并差交笛卡尔积
- 集合运算是 并差交笛卡尔积
- 关系运算是 投影连接除
- 大数据一次处理一个集合(set),不是一个记录(record)
3.2 列转行实操
3.2.1 数据准备
-
建个横表
create table table_grade_wide as( select user_id '学生ID', max(if(course = '语文',score,null)) '语文', max(if(course = '数学',score,null)) '数学', max(if(course = '英语',score,null)) '英语' from table_grade group by user_id ) alter table table_grade_wide change user_id id int;
3.2.2 实操
select * from(
select 学生ID,'语文' course,语文 score from table_grade_wide -- 只需要在第一个select字段中修改别名就好了,因为union的时候,前后的所有的select的列的类型和个数是一致的
union --
select 学生ID,'数学',数学 from table_grade_wide
union
select 学生ID,'英语',英语 from table_grade_wide ) a
where score is not null -- 因为有的同学只考了其中几门课
order by 1; -- 按照最后结果的第一列进行排序
小结
好了,MySQL的行列转换到这里就要告一段落了,希望大家通过上一篇文章——行列转换(一)• MySQL版以及本篇文章的学习,应该对MySQL的行列转换有了了解,学习是永无止境的,接下来,我们会按照这样的方式为大家讲述Oracle中的行列转换,如果大家对于文章的内容、排版等各个方面有什么好的想法,都可以进行沟通交流,也希望我的博客中的内容能为大家在学习的道路上提供一点点助力,我们一起学习,一起进步