搞了几天的存储过程

CREATE procedure R_GzReport_financeTotal
 @YearNum varchar(20)
as
 set @YearNum =ltrim(Rtrim(@YearNum))
begin
 select  @YearNum as YearNum
  
 into table1

 select * from table1
 for xml auto

 create table #temp(
  DeptID  int,
  DeptName varchar(50),
  DeptSequence int,
  [month] int,
  month1 int,
  month2 int,
  month3 int,
  month4 int,
  month5 int,
  month6 int,
  month7 int,
  month8 int,
  month9 int,
  month10 int,
  month11 int,
  month12 int
  )
    insert into #temp
 select distinct(pm.DeptID),
  pm.DeptName,
  pm.DeptSequence,
  month(gr.GatherDate) as [month],
  (select isnull(sum(convert(int,gr.GatherMoney)),'')  where month(gr.GatherDate)=1 ),
  (select isnull(sum(convert(int,gr.GatherMoney)),'') where month(gr.GatherDate)=2),
  (select isnull(sum(convert(int,gr.GatherMoney)),'') where month(gr.GatherDate)=3),
  (select isnull(sum(convert(int,gr.GatherMoney)),'') where month(gr.GatherDate)=4),
  (select isnull(sum(convert(int,gr.GatherMoney)),'') where month(gr.GatherDate)=5),
  (select isnull(sum(convert(int,gr.GatherMoney)),'') where month(gr.GatherDate)=6),
  (select isnull(sum(convert(int,gr.GatherMoney)),'') where month(gr.GatherDate)=7),
  (select isnull(sum(convert(int,gr.GatherMoney)),'') where month(gr.GatherDate)=8),
  (select isnull(sum(convert(int,gr.GatherMoney)),'') where month(gr.GatherDate)=9),
  (select isnull(sum(convert(int,gr.GatherMoney)),'') where month(gr.GatherDate)=10),
  (select isnull(sum(convert(int,gr.GatherMoney)),'') where month(gr.GatherDate)=11),
  (select isnull(sum(convert(int,gr.GatherMoney)),'') where month(gr.GatherDate)=12)
  
 from GatheringRecord gr,Person_Main pm
 where year(gr.GatherDate)=@YearNum
  and ConfirmDate is not NULL
  and gr.perID=pm.id
 group by pm.DeptSequence,pm.DeptID,pm.DeptName,month(gr.GatherDate)
 order by pm.DeptSequence,month(gr.GatherDate)
 
 select
  DeptName,
  avg(DeptSequence)as [sequence],
  isnull(avg(month1),0) as getmoney1,
  isnull(avg(month2),0) as getmoney2,
  isnull(avg(month3),0)  as getmoney3,
  isnull(avg(month4),0)  as getmoney4,
  isnull(avg(month5),0)  as getmoney5,
  isnull(avg(month6),0)  as getmoney6,
  isnull(avg(month7),0)  as getmoney7,
  isnull(avg(month8),0)  as getmoney8,
  isnull(avg(month9),0)  as getmoney9,
  isnull(avg(month10),0)  as getmoney10,
  isnull(avg(month11),0)  as getmoney11,
  isnull(avg(month12),0)  as getmoney12
  
 from #temp
 group by DeptName

 order by avg(DeptSequence) for xml raw

 drop table #temp,table1

end

GO
所有获得收入的部门的年总表（分部门和月份排列）

 

 

 

 

优化后的~~~

 

CREATE procedure R_GzReport_financeTotal
 @YearNum varchar(20)
as
 set @YearNum =ltrim(Rtrim(@YearNum))
begin
 select  @YearNum as YearNum
  
 into table1

 select * from table1
 for xml auto
  

 --创建临时表存放数据
 create table #temp(
  DeptSequence int,
  DeptID int,
  DeptName varchar(50),
  gmm int,
  summon int)
 
 insert into #temp
 
 select pm.deptsequence DeptSequence,
  pm.deptid DeptID,
  pm.deptname DeptName,
  month(gr.GatherDate) gmm,
  isnull(sum(convert(int,gr.GatherMoney)),'') summon
 from person_main pm,gatheringrecord gr

 where year(gr.GatherDate)=@YearNum
  and ConfirmDate is not NULL
  and gr.perID=pm.id
 group by pm.DeptSequence,pm.DeptID,pm.DeptName,month(gr.GatherDate)
 
 
 SELECT DeptID,
  DeptSequence,
  DeptName,
  [getmoney1]=isnull(SUM(CASE gmm WHEN '1' THEN summon END),0),--列转行，n月份表示成getmoney[n]的形式
  [getmoney2]=isnull(SUM(CASE gmm WHEN '2' THEN summon END),0),
  [getmoney3]=isnull(SUM(CASE gmm WHEN '3' THEN summon END),0),
  [getmoney4]=isnull(SUM(CASE gmm WHEN '4' THEN summon END),0),
  [getmoney5]=isnull(SUM(CASE gmm WHEN '5' THEN summon END),0),
  [getmoney6]=isnull(SUM(CASE gmm WHEN '6' THEN summon END),0),
  [getmoney7]=isnull(SUM(CASE gmm WHEN '7' THEN summon END),0),
  [getmoney8]=isnull(SUM(CASE gmm WHEN '8' THEN summon END),0),
  [getmoney9]=isnull(SUM(CASE gmm WHEN '9' THEN summon END),0),
  [getmoney10]=isnull(SUM(CASE gmm WHEN '10' THEN summon END),0),
  [getmoney11]=isnull(SUM(CASE gmm WHEN '11' THEN summon END),0),
  [getmoney12]=isnull(SUM(CASE gmm WHEN '12' THEN summon END),0)
 FROM #temp
 GROUP BY DeptID,DeptSequence,DeptName
 order by DeptSequence for xml raw

 drop table #temp,table1

end
GO