关于sqlserver中涉及关联查询、转换显示、百分比转换等函数使用实例

select a.sjkzbm,a.fykzdl,a.yszs,b.spzje,c.yyje,(a.yszs-b.spzje-c.yyje)as kyje,CONCAT(Convert(decimal(18,2),(b.spzje+c.yyje)/yszs*100) ,'%') as zxbl from (select sjkzbm,fykzdl,sum(budgetaccount) as yszs from (select b.id,sjkzbm,a.sjkm,fykzdl=case when fykzdl='0' then '综合部 (业务管理费)' 
when  fykzdl='1' then '综合部(会议)'
when  fykzdl='2'  then 'IT部' 
when  fykzdl='3' then '综合部(宣传费)'
when  fykzdl='4' then '各部门各自管理' else '空' end
from uf_bxkzdzb a, fnabudgetfeetype b where charindex(cast(b.id as varchar(max))+',',sjkm)>0) x right join 
(select b.* from FnaBudgetInfo 
a join FnaBudgetInfoDetail b on a.id=b.budgetinfoid  
join FnaYearsPeriods c on a.budgetperiods=c.id 
join fnabudgetfeetype d on b.budgettypeid=d.id 
where  a.organizationtype=2 and a.status=1) y on x.id=y.budgettypeid  where fykzdl is not null and  fykzdl <> '各部门各自管理'group by fykzdl,sjkzbm) a left join 
(select sjkzbm, fykzdl,sum(amount) as spzje from (select b.id,a.sjkm,sjkzbm,fykzdl=case when fykzdl='0' then '综合部 (业务管理费)' 
when  fykzdl='1' then '综合部(会议)'
when  fykzdl='2'  then 'IT部' 
when  fykzdl='3' then '综合部(宣传费)'
when  fykzdl='4' then '各部门各自管理' else '空' end
from uf_bxkzdzb a, fnabudgetfeetype b where charindex(cast(b.id as varchar(max))+',',sjkm)>0) m  right join 
(select a.* from FnaExpenseInfo a 
join FnaYearsPeriods b on b.startdate <= a.occurdate and b.enddate >= a.occurdate 
join FnaBudgetfeeType c on a.subject = c.id 
where a.status=0) n on m.id=n.subject where fykzdl is not null and  fykzdl <> '各部门各自管理' group by fykzdl,sjkzbm) b on a.fykzdl=b.fykzdl left join 
(select sjkzbm,fykzdl,isnull(sum(amount),0) as yyje from (select b.id,sjkzbm,a.sjkm,fykzdl=case when fykzdl='0' then '综合部 (业务管理费)' 
when  fykzdl='1' then '综合部(会议)'
when  fykzdl='2'  then 'IT部' 
when  fykzdl='3' then '综合部(宣传费)'
when  fykzdl='4' then '各部门各自管理' else '空' end
from uf_bxkzdzb a, fnabudgetfeetype b where charindex(cast(b.id as varchar(max))+',',sjkm)>0) m  left join 
(select a.* from FnaExpenseInfo a 
join FnaYearsPeriods b on b.startdate <= a.occurdate and b.enddate >= a.occurdate 
join FnaBudgetfeeType c on a.subject = c.id 
where a.status=1) n on m.id=n.subject where fykzdl is not null and  fykzdl <> '各部门各自管理' group by fykzdl,sjkzbm) c on a.fykzdl=c.fykzdl