在写到一半代码的时候，需要统计指定行的乘积。想到SQL中有
EXP(SUM(LOG(字段)))的新函数试试。完全代码如下<??http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vcD4NCjxwcmUgY2xhc3M9"brush:sql;"> -- ============================================= -- Author: -- Create date: <2015-9-11> -- Description: <统计COB品质状况表> -- ============================================= ALTER Proc UP_Proc_COB ( @year as char(4) ) as begin create table #tmpCOB( 项目 varchar(20), 月份 varchar(20), 百分比 decimal(8,4) default (0) ); --declare @year as char(4) --set @year='2015' declare @yearmonth as char(6) declare @i int set @i=1 declare @month char(2) while @i<=12 BEGIN set @month=right(@i+100,2) set @yearmonth =@year+right(@i+100,2) if(@yearmonth<=CONVERT(char(6),getdate(),112)) begin --统计当月绑定合格率 insert into #tmpCOB(项目,月份,百分比) select '1.绑定合格率' as 项目, @month 月份, cast(round(sum(测试数量-不良数量)*1.0/sum(测试数量),4) as numeric(8,4)) 合格率 from 制程COB绑测 where CONVERT(char(6),日期,112)=@yearmonth --统计当月外观合格率 insert into #tmpCOB(项目,月份,百分比) select '2.外观合格率' as 项目, @month 月份, cast(round(sum(测试数量-不良数量)*1.0/sum(测试数量),4) as numeric(8,4)) 合格率 from 制程COB外观 where CONVERT(char(6),日期,112)=@yearmonth --统计成测合格率 insert into #tmpCOB(项目,月份,百分比) select '3.终测合格率' as 项目, @month 月份, cast(round(sum(测试数量-不良数量)*1.0/sum(测试数量),4) as numeric(8,4)) 合格率 from 制程COB成测 where CONVERT(char(6),日期,112)=@yearmonth --统计FQC合格率 insert into #tmpCOB(项目,月份,百分比) select '4.FQC合格率' as 项目, @month 月份, cast(round(sum(检验数量-不合格数量)*1.0/sum(检验数量),4) as numeric(8,4)) 合格率 from 制程FQC_COB_检验 where CONVERT(char(6),检验日期,112)=@yearmonth end set @i=@i+1 END select * into #COB from #tmpCOB pivot(max(百分比) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) a select 项目,[01] 一月,[02] 二月,[03] 三月,[04] 四月,[05] 五月,[06] 六月,[07] 七月,[08] 八月,[09] 九月,[10] 十月,[11] 十一月,[12] 十二月 FROM #COB union all ---处理指定行的列乘积计算 /* --方法一: CASE WHEN 行转列 之后PIVOT函数指定列 select * from (select '6.制程一次通过率' as 项目,月份,ROUND(sum(绑定百分比)*sum(外观百分比)*sum(终测百分比),4) as 百分比 from( select 项目,月份, CASE WHEN 项目='1.绑定合格率' then 百分比 else 0 end 绑定百分比 , CASE WHEN 项目='2.外观合格率' then 百分比 else 0 end 外观百分比 , CASE WHEN 项目='3.终测合格率' then 百分比 else 0 end 终测百分比 from #tmpCOB where 项目<>'4.FQC合格率' ) A group by A.月份) as c pivot(max(百分比) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) b */ --方法二 直接采用EXP(SUM(LOG(字段))) SELECT '6.制程一次通过率', ROUND(EXP(SUM(LOG([01]))),4) as 一月, ROUND(EXP(SUM(LOG([02]))),4) as 二月, ROUND(EXP(SUM(LOG([03]))),4) as 三月, ROUND(EXP(SUM(LOG([04]))),4) as 四月, ROUND(EXP(SUM(LOG([05]))),4) as 五月, ROUND(EXP(SUM(LOG([06]))),4) as 六月, ROUND(EXP(SUM(LOG([07]))),4) as 七月, ROUND(EXP(SUM(LOG([08]))),4) as 八月, ROUND(EXP(SUM(LOG([09]))),4) as 九月, ROUND(EXP(SUM(LOG([10]))),4) as 十月, ROUND(EXP(SUM(LOG([11]))),4) as 十一月, ROUND(EXP(SUM(LOG([12]))),4) as 十二月 FROM #COB where 项目<>'4.FQC合格率' union all select case when 项目='FQC合格率目标' then '5.FQC合格率目标' else '7.一次直通率目标' end as 项目, 一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月 from 制程FQC目标 where 系列='COB' and 年份=@year drop table #tmpCOB drop table #COB end

效果


?