oracle列转行筛选,oracle查询中行转列、列转行以及PIVOT、UNPIVOT使用

行转列

行转列的大致意思是,将表中多行数据转成一行的多个字段输出。需要行转列的业务逻辑通常是,将表中的一个字段分类统计后作为多个结果字段输出。

一个简单例子:

有一个学生表,有姓名、学科、成绩三个字段

Java代码 180208863_1_20200111061517361.pngcreate table studentScores(

username varchar2(20),

subject  varchar2(30),

score    float

)

insert into studentScores values('justin','语文',90);

insert into studentScores values('justin','英语',80);

insert into studentScores values('justin','数学',90);

insert into studentScores values('jia','语文',90);

insert into studentScores values('jia','英语',80);

insert into studentScores values('jia','数学',90);

业务场景是我需要将每个学生的各科成绩拉成一行,方便查看统计。大致效果如下图

180208863_2_20200111061517392.png

通常都会用到GROUP BY 和 CASE WHEN

Java代码 180208863_1_20200111061517361.pngselect username,

max(case subject when '语文' then score else 0 end) as 语文,

max(case subject when '数学' then score else 0 end) as 数学,

max(case subject when '英语' then score else 0 end) as 英语

from studentScores

group by username

pivot函数

11g之后oracle提供了一个pivot函数来专门实现这个行转列功能,上面的sql语句可用下面语句代替

Java代码 180208863_1_20200111061517361.pngselect username,"'语文'","'数学'","'英语'" //这里也能用*,用pivot函数查出来的字段名称就是‘语文’注意这两个单引号也是名称一部分,所以这行要用双引号括起来

from studentScores

pivot(max(score)   //聚合列取值

for subject in ('语文','数学','英语')) //定义哪个列的哪些值 转成新的列  以及新列的顺序

pivot函数有个缺点。使用GROUP BY + CASE WHEN写法时,查询结果行数就是 group by字段非重复值的数量。而pivot查询的结果相当于group by除聚合列和待行转列列 之外所有列的结果。比如为studentScores表添加一个teacher字段,输出不同值,还是上面的pivot查询语句,结果如下:

180208863_3_20200111061517502.png

还有试过不手动输入上面‘语文’等列,而是用SELECT DISTINCT SUBJECT FROM STUDENTSCORES子查询结果作为in的值,结果发现是不行的。所以抛开性能(不知道性能有没有提示)不谈,除了sql语句比较简洁外,这个函数没什么优越性

列转行

列转行更容易理解,将表中多个列(一般是同一纬度相关)每个列拉成一行数据。

比如有个项目供应量表,固定供应方有海外、本地、南方、北方四个,表如下:

Java代码 180208863_1_20200111061517361.pngcreate table projectDetail(

projectName varchar2(20),

overseaSupply int,

nativeSupply int,

southsupply int,

northsupply int);

insert into projectdetail values('A',100,100,100,100);

insert into projectdetail values('B',50,50,50,50);

insert into projectdetail values('C',200,200,200,200);

现在业务需求是展示项目名,供应方,供应量三个字段,需要展示结果如下:

180208863_4_20200111061517720.png

查询逻辑一般就是查询相关字段取统一别名,然后用union all将结果连接

Java代码 180208863_1_20200111061517361.pngselect projectname,'overseasupply' as supplier,overseasupply as supplysum

from projectdetail

union all

select projectname,'nativeSupply' as supplier,nativeSupply as supplysum

from projectdetail

union all

select projectname,'southsupply' as supplier,southsupply as supplysum

from projectdetail

union all

select projectname,'northsupply' as supplier,northsupply as supplysum

from projectdetail

unpivot

使用unpivot函数获得上面相同效果的sql语句如下

Java代码 180208863_1_20200111061517361.pngselect projectname, supplier, supplysum   //与下面别名相同

from projectdetail unpivot(supplysum //为列转行中多列变成一列的 值取别名

for supplier    //在结果中成为一列的原多列取统一别名

in(overseasupply,

nativeSupply,

southsupply,

northsupply))

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值