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

;

效果图如下：

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

 

转载于:https://www.cnblogs.com/jerryRecord/p/4860078.html