关于SQL的统计数据补录

数据库是DB2，没有使用特殊的函数等，算做是标准SQL吧！

本来还有更长的，一想到看起来不方便，所以找了好久终于找出这个认为是相对最短的SQL来做鸡，现在简单的杀一下看看。

这是做报表时用到的所有数据，如果正常的程序处理一般情况下是不需要补数据的，但是在做统计时有一些类别下面没有数据，那么查询出的结果便一定缺少某些分类信息，所以在即使没有该信息也要显示出来的情况下便需要将这些数据人工的补出来，之后连同正常查出来的数据一起进行数据合并，这样便不会缺少某些分类了。

方法便如下SQL了。

select re.or,re.catename,sum(re.bz) as bz,sum(re.cg) as cg,sum(re.jj) as jj from

(

select 1 as or,'突发事件' as catename,0 as bz,0 as cg,0 as jj from processrecord where prid=(select max(prid) from processrecord) union

select 2 as or,'配置' as catename,0 as bz,0 as cg,0 as jj from processrecord where prid=(select max(prid) from processrecord) union

select 3 as or,'系统增强' as catename,0 as bz,0 as cg,0 as jj from processrecord where prid=(select max(prid) from processrecord) union

select 4 as or,'服务请求' as catename,0 as bz,0 as cg,0 as jj from processrecord where prid=(select max(prid) from processrecord) union

select 5 as or,'其他' as catename,0 as bz,0 as cg,0 as jj from processrecord where prid=(select max(prid) from processrecord) union

select case when fi.catename='突发事件' then 1 when fi.catename='配置' then 2  when fi.catename='系统增强' then 3

when fi.catename='服务请求' then 4 when fi.catename='其他' then 5 end as or,fi.catename,fi.bz,fi.cg,fi.jj

from

(

select ff.catename,sum(ff.bz) as bz,sum(ff.cg) as cg,sum(ff.jj) as jj from (

select f.catename,case when f.cate='标准变更' then f.num else 0 end as bz,case when f.cate='常规变更' then f.num else 0 end as cg,case when f.cate='紧急变更' then f.num else 0 end as jj 

from (

select count(re.prid) as num,re.catename,re.cate  from

(

select r.prid,max(r.catename) as catename,max(r.cate) cate from

(

select pr.prid ,pi.pivarvalue as catename,'' as cate from processrecord as pr left join processinfo as pi on pr.prid=pi.prid where pi.pidid=1017 and pr.prtype='CD' 

 union

select pr.prid ,'' as catename,pi.pivarvalue as cate from processrecord as pr left join processinfo as pi on pr.prid=pi.prid where pi.pidid=1022 and pr.prtype='CD' 

) as r group by r.prid

) as re where re.cate != '' group by re.catename,re.cate

) as f

) as ff group by ff.catename

) as fi

) as re group by re.or,re.catename

 

 

其中：

select 1 as or,'突发事件' as catename,0 as bz,0 as cg,0 as jj from processrecord where prid=(select max(prid) from processrecord) 便是查询出某一类的信息了，后面的prid=(select max(prid) from processrecord)是为了只查出一条数据来，如果没有那数据可就多了，union是有多个分类情况下使用的。