【SQL应知应会】行列转换（三）• Oracle版

欢迎来到爱书不爱输的程序猿的博客, 本博客致力于知识分享，与更多的人进行学习交流

本文收录于SQL应知应会专栏,本专栏主要用于记录对于数据库的一些学习，有基础也有进阶，有MySQL也有Oracle

行列转换 • Oracle版

oracle的行列转换

前言

前面的两篇文章【SQL应知应会】行列转换（一）• MySQL版和【SQL应知应会】行列转换（二）• MySQL版详细的讲述了MySQL的行列转换，而今天这篇文章，讲的是Oracle中的行列转换，其中有与MySQL共同的方法，也有Oracle专用的方法，希望对大家在学习上能有所帮助。

1.数据准备

1.1知识点补充

createtable table_grade(id int,user_name varchar(20),course varchar(10),score decimal(5,2));

oracle 中没有int，有integer类型，不过兼容 oracle没有double
oracle没有varchar类型,有varchar2,不过使用varchar也可以建
decimal有,但是建进去会变成number,number既支持整数又支持小数,最大可以设置到38位

droptable table_grade purge;

oracle 删除的时候要加purge,因为oracle有回收站机制,不加purge的话会将删除的东西放在回收站中,这样的话,之后还需要进行一个recyclebin的操作

PURGE RECYCLEBIN;-- 清空某一用户的所有表空间下的对象

1.2 知识点应用

createtable table_grade (id number(38),user_name varchar(20),course varchar(10),score decimal(5));insertinto table_grade values('1','张龙','语文','78');insertinto table_grade values('2','张龙','数学','95');insertinto table_grade values('3','张龙','英语','81');insertinto table_grade values('4','赵虎','语文','97');insertinto table_grade values('5','赵虎','数学','78');insertinto table_grade values('6','赵虎','英语','91');insertinto table_grade values('7','王五','语文','81');insertinto table_grade values('8','王五','数学','55');insertinto table_grade values('9','王五','英语','75');insertinto table_grade values('10','马六','语文','87');insertinto table_grade values('11','马六','数学','65');insertinto table_grade values('12','马六','英语','75');commit;-- 插入数据后，需要进行一个提交

2.行转列

2.1通用的行转列(Mysql和Oracle都能用) ——> case when

select id "学生ID",-- oracle中单引号被识别为字段里的一个值,所以别名使用双引号 -- 我的猜想:好像加as就可以用单引号了--> 没验证成功(selectmax(user_name)from table_grade where id = t.id) user_name,max(casewhen course ='语文'then score end)"语文",max(casewhen course ='数学'then score end)"数学",max(casewhen course ='英语'then score end)"英语"from table_grade
groupby id;

2.2 私有方法的行转列(Oracle用) ——> decode()

select id "学生ID",(selectmax(user_name)from table_grade where id = t.id) user_name,max(decode(course,'语文',score,''))"语文",max(decode(course,'数学',score,''))"数学",max(decode(course,'英语',score,''))"英语"from table_grade t
groupby id orderby1-- decode(字段或字段的运算，值1，值2，值3）-- 当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3-- 当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

2.3 终极方法(Oracle用) ——> 透视表函数pivot()

2.3.1理论

select*from table_name
pivot(max(column_name)-- 行转列后的列的值value,聚合函数是必须要有的for column_name in(value_1,value_2,value_3))-- 需要行转列的列及其对应的属性1/2/3

2.3.2 应用

select*from table_grade 
pivot(max(score)-- 此处max()改成min/sum/avg是一样的,因为此时的table_grade表中,一个学生每门课就有一个成绩for course in('语文' 语文,'数学' 数学,'英语' 英语))-- 先写值value,因为course是字符串,所以加单引号   后面是别名

2.3.3美化

select user_name,语文,数学,英语 from table_grade 
pivot(max(score)for course in('语文' 语文,'数学' 数学,'英语' 英语))orderby user_name 

3.列转行

3.1

with as

• with as 是oracle特有写法,可以将as中的查询当做一个表来使用

with table_grade_wide 
as(select user_name "姓名",max(casewhen course ='语文'then score end)"语文",max(casewhen course ='数学'then score end)"数学",max(casewhen course ='英语'then score end)"英语"from table_grade t
         groupby user_name)

• 然后就可以当做一个表使用了

select*from table_grade_wide 

3.2进行一个列转行的操作(这是通用方法) ——> union

-select*from(select 姓名,'语文' course,语文 score from table_grade_wide
      union-- 如果此处使用union all 的话,和union是没有区别的,前一句是赋值语文,下一句是赋值数学,二者union不会去重,相当于产生一个course字段分别是语文和数学,所以二者去重是不会消掉数据的select 姓名,'数学',数学 from table_grade_wide
      unionselect 姓名,'英语',英语 from table_grade_wide)-- a -- 这里这个虚拟的表就可以当做真实的表来使用了，所以就不需要给它别名了 -- ？为什么可以当做真实的表用了？这是oracle的原因吗-- where score id not null      -- 这句如果原来表中有的学生就没有其中一门课的数据，比如王五和马六只有两门课的数据，那就可以通过这句话来解决table_grade_wide表中王五和马六对应的没有的那门课的null;如果表中的学生有课但是成绩是null,那就没有必要使用这句代码,不然就把学生拥有的那门课删掉了,相当于造成了数据丢失orderby 姓名 

3.3 列转行终极方法(Oracle用) ——> unpivot()

with table_grade_wide 
as(select user_name "姓名",max(casewhen course ='语文'then score end)"语文",max(casewhen course ='数学'then score end)"数学",max(casewhen course ='英语'then score end)"英语"from table_grade t
         groupby user_name)select 姓名 user_name,course,score from table_grade_wide unpivot(score for course in(语文,数学,英语))

3.4 一个学生一门课有多次成绩的时候

• 数据准备
• 代码

# 求一个学生每门课程近三次考试中最高的成绩SELECT t1.s_id,t1.c_id,MAX(t1.score)AS best_score
FROM(SELECT s_id,c_id, score, 
         ROW_NUMBER()OVER(PARTITIONBY s_id,c_id ORDERBY exam_date DESC)AS rn
  FROM sc
) t1
WHERE t1.rn <=3GROUPBY t1.s_id,t1.c_id

小结

好了，Oracle的行列转换到这里就要告一段落了，相信大家通过前两篇文章【SQL应知应会】行列转换（一）• MySQL版和【SQL应知应会】行列转换（二）• MySQL版，应该对MySQL的行列转换有了了解，并且通过今天这篇文章对Oracle的行列转换也进行了学习，应该对MySQL和Oracle的行列转换的相同以及不同之处有所了解，那么数据库的行列转换这一模块我们就告一段落了，接下来还会给大家带来其他方面的知识讲解，希望能对大家有所帮助