[MSSQL]结果集直接运算

实际工作中经常碰到占比，比如XX部门超领金额占总金额的占比，离职人员占总人数占比等等。一直都是存取变量，计算结果。今天尝试了下结果集直接进行四则运算

-- =============================================   -- Author: <David Gong>   -- Create date: <2015-9-23>   -- Description: <材料溢领分析>    -- ============================================= alter Proc [dbo].[UP_Excess_Material](    @year  as char(4))asbegincreate 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 intset @i=1declare @month char(2)while @i<=12BEGIN   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+1END---材料总金额select * into #tempallfrom #tmp pivot(max(金额) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) aselect 项目,[01] as 一月,[02] 二月,[03] 三月,[04] 四月,[05] 五月,[06] 六月,[07] 七月 ,[08] 八月 ,[09] 九月 ,[10] 十月,[11] 十一月,[12] 十二月  from  #tempallunion 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 aunion allselect '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.[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  项目='3.生产部金额') as aunion allSELECT '9.'+[项目]       ,[一月]      ,[二月]      ,[三月]      ,[四月]      ,[五月]      ,[六月]      ,[七月]      ,[八月]      ,[九月]      ,[十月]      ,[十一月]      ,[十二月]  FROM [OA].[dbo].[超领目标]  where 年份=@yeardrop table #tmpdrop table #tempallend