实际工作中经常碰到占比，比如XX部门超领金额占总金额的占比，离职人员占总人数占比等等。一直都是存取变量，计算结果。
今天尝试了下结果集直接进行四则运算
-- =============================================
-- Author:
-- Create date: <2015-9-23> -- Description: <材料溢领分析> -- ============================================= alter Proc [dbo].[UP_Excess_Material] ( @year as char(4) ) as begin create table #tmp( 项目 varchar(20), 月份 varchar(20), 金额 decimal(16,2) default (0) ); --declare @year as char(4) --set @year='2015' declare @yearmonth as char(6) declare @01 decimal(16,2),@02 decimal(16,2),@03 decimal(16,2),@04 decimal(16,2),@05 decimal(16,2),@06 decimal(16,2), @07 decimal(16,2),@08 decimal(16,2),@09 decimal(16,2),@10 decimal(16,2),@11 decimal(16,2),@12 decimal(16,2) --定义变量 declare @01_wc decimal(16,2),@02_wc decimal(16,2),@03_wc decimal(16,2),@04_wc int,@05_wc decimal(16,2),@06_wc decimal(16,2),@07_wc decimal(16,2),@08_wc decimal(16,2),@09_wc decimal(16,2),@10_wc decimal(16,2),@11_wc decimal(16,2),@12_wc decimal(16,2) --定义离职总人数变量 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 #tmp(项目,月份,金额) select '1.材料总金额' as 项目, @month 月份,sum(LA013) as 金额 from MOCTC inner join MOCTE ON TC001=TE001 AND TC002=TE002 inner join INVLA ON TE001=LA006 AND TE002=LA007 AND TE003=LA008 where TC008='54' AND TC001<>'5402' AND CONVERT(char(6),TC003,112)=@yearmonth --超领金额 union all select '2.溢领材料金额' as 项目, @month 月份,sum(LA013) as 金额 from MOCTC inner join MOCTE ON TC001=TE001 AND TC002=TE002 inner join INVLA ON TE001=LA006 AND TE002=LA007 AND TE003=LA008 where TC008='54' AND TC001='5402' AND CONVERT(char(6),TC003,112)=@yearmonth union all --超领按部门金额 select 项目,月份,sum(金额) as 金额 from ( select case when ME002 in ('BL车间','LCM车间') then '3.生产部金额' when ME002 ='开发部' then '4.开发部金额' when ME002 ='工程部' then '5.工程部金额' when ME002 ='采购部' then '6.采购部金额' else ME002 end as 项目, @month 月份,LA013 as 金额 from MOCTC inner join MOCTE ON TC001=TE001 AND TC002=TE002 inner join INVLA ON TE001=LA006 AND TE002=LA007 AND TE003=LA008 inner join CMSME ON TC021=ME001 where TC008='54' AND TC001='5402' AND CONVERT(char(6),TC003,112)=@yearmonth) K group by K.项目,K.月份 end set @i=@i+1 END ---材料总金额 select * into #tempall from #tmp pivot(max(金额) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) a select 项目,[01] as 一月,[02] 二月,[03] 三月,[04] 四月,[05] 五月,[06] 六月,[07] 七月 ,[08] 八月 ,[09] 九月 ,[10] 十月,[11] 十一月,[12] 十二月 from #tempall union all ----两个结果集对应的字段进行四则运算 select '7.总溢领率' as 项目 , cast(round(a.[01]/b.[01],4) as numeric(5,4)), cast(round(a.[02]/b.[02],4) as numeric(5,4)), cast(round(a.[03]/b.[03],4) as numeric(5,4)), cast(round(a.[04]/b.[04],4) as numeric(5,4)), cast(round(a.[05]/b.[05],4) as numeric(5,4)), cast(round(a.[06]/b.[06],4) as numeric(5,4)), cast(round(a.[07]/b.[07],4) as numeric(5,4)), cast(round(a.[08]/b.[08],4) as numeric(5,4)), cast(round(a.[09]/b.[09],4) as numeric(5,4)), cast(round(a.[10]/b.[10],4) as numeric(5,4)), cast(round(a.[11]/b.[11],4) as numeric(5,4)), cast(round(a.[12]/b.[12],4) as numeric(5,4)) from (select * from #tempall where 项目='1.材料总金额') as b, (select * from #tempall where 项目='2.溢领材料金额') as a union all select '8.生产部领率' as 项目 , cast(round(a.[01]/b.[01],4) as numeric(5,4)), cast(round(a.[02]/b.[02],4) as numeric(5,4)), cast(round(a.[03]/b.[03],4) as numeric(5,4)), cast(round(a.[04]/b.[04],4) as numeric(5,4)), cast(round(a.[05]/b.[05],4) as numeric(5,4)), cast(round(a.[06]/b.[06],4) as numeric(5,4)), cast(round(a.[07]/b.[07],4) as numeric(5,4)), cast(round(a.[08]/b.[08],4) as numeric(5,4)), cast(round(a.[09]/b.[09],4) as numeric(5,4)), cast(round(a.[10]/b.[10],4) as numeric(5,4)), cast(round(a.