oarcle报表形式的写法!

技术·学习 · 2018-03-30 · 181 人浏览

通常我们在对数据在统计的时候会有类似报表形式的写法:如下

人名/类型 A B C D

张三 1 2 3 4
李四 2 3 4 5

这里我们使用的sql如下:

select * from (
select a.id,a.xm xm,a.unitid,count(b.id) con,
nvl(sum(case when b.type='1' then 1 end),0) word,
nvl(sum(case when b.type='2' then 1 end),0) excel,
nvl(sum(case when b.type='3' then 1 end),0) ppt,
nvl(sum(case when b.type='4' then 1 end),0) txt,
nvl(sum(case when b.type='5' then 1 end),0) img,
nvl(sum(case when b.type='6' then 1 end),0) zip,
nvl(sum(case when b.type='7' then 1 end),0) video,
nvl(sum(case when b.type='8' then 1 end),0) pdf,
nvl(sum(case when b.type='6' then 1 end),0) unknow
from jcsjzx_jzgjbxx a
left join zyk_zykfj b on a.zgh = b.operno and a.unitid = b.unitid
group by a.id,a.xm,a.unitid,b.type
) v

查询结果如下:

2024-03-10T10:42:44.png
这里我们可使用oracle 新的函数pivot() 来对它改写,使之能够变通:

select * from (
select a.id con,/*这里代表要统计的列,使用pivot 会自动将 用到的列 进行group by 分组*/
c.id ,c.jcmc jcmc_cn,a.type as type 
from zyk_jcfj a 
left join zyk_jczj b on a.zb = b.id
left join zyk_jcxx c on b.jc = c.id
)
pivot(
     count(con) for type in 
     ('1' word,'2' excel,'3' ppt,'4' txt,'5' img,'6' zip,'7' video,'8' pdf,'9' unknow) 
);

从上述看,sql简单了许多,查询结果如下:

2024-03-10T10:44:48.png

Theme Jasmine by Kent Liao