行变列,列变行在生成报表的时候经常遇到,行变列叫做"Pivot”, 反之叫做"Unpivot”。 在Oracle11g之前,一般都是通过case来实现,但是Oracle11g及其以后直接支持PIVOT和UNPIVOT的操作。
pivot
语法:
SELECT * FROM (元数据) PIVOT ( SUM(Score/*行转列后 列的值*/) FOR coursename/*需要行转列的列*/ IN (转换后列的值) )for 可以看成循环,for前是行转列后显示的值(这里必须使用聚合函数),for后是循环的字段(一般是有限的固定值,如字典等) in (里是对for后的循环字段处理,比如起别名,做运算等)
之前写sql的处理方式,遇到放在一起显示成一行的数据时,都是使用left join...连接成一条记录显示,这样效率不仅低而且sql看起来还很乱,如下所示:想将数据表按照数据来源统计求和
SELECT
to_char(to_date(t1.tjrq,'yyyymmdd'),'yyyy"年"mm"月"dd"日"') tjrq,
nvl ( t1.ztrs, 0 ) yjjj,
nvl ( t2.ztrs, 0 ) qyxf,
nvl ( t3.ztrs, 0 ) dgmhs
from
(SELECT tjrq,sum( ztrs ) ztrs FROM ( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM
A GROUP BY zrq, sjly, tjrq )
where tjrq BETWEEN '20220814' and '20220914' and sjly='页面' GROUP BY sjly,tjrq) t1
left join
(SELECT tjrq,sum( ztrs ) ztrs FROM ( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM A
GROUP BY zrq, sjly, tjrq )
where tjrq BETWEEN '20220814' and '20220914' and sjly='接口' GROUP BY sjly,tjrq) t2 on
t1.tjrq=t2.tjrq
left join
(SELECT tjrq,sum( ztrs ) ztrs FROM ( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM A
GROUP BY zrq, sjly, tjrq )
where tjrq BETWEEN '20220814' and '20220914' and sjly='导入' GROUP BY sjly,tjrq) t3 on
t2.tjrq=t3.tjrq
order by t1.tjrq desc
虽说结果也是正确的,但无疑不是最优方案
使用pivot改造
select
to_char(to_date(tjrq,'yyyymmdd'),'yyyy"年"mm"月"dd"日"') tjrq,
nvl ( yjjj, 0 ) yjjj,
nvl ( qyxf, 0 ) qyxf,
nvl ( dgmhs, 0 ) dgmhs
from
(
select sjly,tjrq,sum(ztrs) ztrs from (SELECT zrq, sjly,tjrq, min( ztrs ) ztrs FROM
A GROUP BY zrq, sjly ,tjrq) group by sjly,tjrq
)
pivot (max(ztrs) for sjly in ('页面' as yjjj,'接口' as qyxf,'导入' as dgmhs))
where tjrq BETWEEN '20220814' and '20220914'
order by tjrq desc
教程例子:贴图助理解
unpivot
列转行,就是将一行结果拆成多行显示
语法:
select 字段 from 数据集 unpivot(自定义列名/*列的值*/ for 自定义列名 in(列名))
如上图
可以使用union 或者union all
文章来源:https://www.toymoban.com/news/detail-413849.html
文章来源地址https://www.toymoban.com/news/detail-413849.html
到了这里,关于Oracle行转列(pivot)和Oracle列转行(unpivot)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!