转载

with as用法及行转列的用法

用with as 建立临时表(产品使用记录)，想要达到什么目的？统计产品每日使用情况

with productD as(

select to_date('2015-1-1','yyyy-MM-dd') as dt,'A01' as productNo,'10' as pNum from dual

union all

select to_date('2015-1-1','yyyy-MM-dd') as dt,'A02' as productNo,'10' as pNum from dual

union all

select to_date('2015-1-2','yyyy-MM-dd') as dt,'A01' as productNo,'10' as pNum from dual

union all

select to_date('2015-1-2','yyyy-MM-dd') as dt,'A01' as productNo,'24' as pNum from dual

union all

select to_date('2015-1-3','yyyy-MM-dd') as dt,'A01' as productNo,'25' as pNum from dual

union all

select to_date('2015-1-4','yyyy-MM-dd') as dt,'A01' as productNo,'25' as pNum from dual

union all

select to_date('2015-1-5','yyyy-MM-dd') as dt,'A01' as productNo,'26' as pNum from dual

union all

select to_date('2015-1-5','yyyy-MM-dd') as dt,'A01' as productNo,'27' as pNum from dual

union all

select to_date('2015-1-5','yyyy-MM-dd') as dt,'A01' as productNo,'20' as pNum from dual

union all

select to_date('2015-1-6','yyyy-MM-dd') as dt,'A01' as productNo,'20' as pNum from dual

union all

select to_date('2015-1-6','yyyy-MM-dd') as dt,'A02' as productNo,'20' as pNum from dual

)

select d.productNo,nvl(sum(case to_char(d.dt,'yyyyMMdd') when '20150101' then pnum end ),0) as d1 ,

nvl(sum(case to_char(d.dt,'yyyyMMdd') when '20150102' then pnum end ),0) as d2,

nvl(sum(case to_char(d.dt,'yyyyMMdd') when '20150103' then pnum end ),0) as d3,

nvl(sum(case to_char(d.dt,'yyyyMMdd') when '20150104' then pnum end ),0) as d4,

nvl(sum(case to_char(d.dt,'yyyyMMdd') when '20150105' then pnum end ),0) as d5,

nvl(sum(case to_char(d.dt,'yyyyMMdd') when '20150106' then pnum end ),0) as d6

from productD d

group by d.productNo

--order by d.productNo desc

;

效果图如下：

with as用法及行转列的用法

不求一步登天，只希望自己偶尔能学习，解决自己遇到的问题；

正文到此结束

所属分类：编程技术

本文标签： http 统计 src Select UI 产品
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

with as用法及行转列的用法

热门推荐

相关文章

说给你听

本文目录

随机标签

书籍教程

近期评论

网站信息

其他链接

关于本站

问题交流