sql server 2008关联统计(sum、count、case、group by)

--总表 统计
select ProjectType ,CompletedTime,
count(distinct ProjectId) as ProjectCount,

--COUNT(distinct RelatedTabId) as WellCount,

COUNT(distinct position) as PositionCount,

COUNT(distinct ManageMan) as ManageManCount,

COUNT(distinct ProjectType) as ProjectTypeCount,

--根据ManageUnit 和 RelatedTabName统计个数
sum(case when ManageUnit=1 and RelatedTabName='A' then 1 else 0 end) as MCCount, 

sum(case when ManageUnit=1 and RelatedTabName='B' then 1 else 0 end) as 

MCCount2, 

sum(case when ManageUnit=1 and RelatedTabName='C' then 1 else 0 end) as 

MCCount3, 

sum(case when ManageUnit=1 and RelatedTabName='D' then 1 else 0 end) as 

MCCount4, 

sum(case when ManageUnit=3 and RelatedTabName='A' then 1 else 0 end) as MDCount, 

sum(case when ManageUnit=3 and RelatedTabName='B' then 1 else 0 end) as 

MDCount2, 

sum(case when ManageUnit=3 and RelatedTabName='C' then 1 else 0 end) as 

MDCount3, 

sum(case when ManageUnit=3 and RelatedTabName='D' then 1 else 0 end) as 

MDCount4, 

sum(case when ManageUnit=3 and RelatedTabName='E' then 1 else 0 end) as 

MDCount5, 


--根据 RelatedTabName统计个数
sum(case when RelatedTabName='A' then Area else 0 end) as MArea, 

sum(case when RelatedTabName='B' then Area else 0 end) as MArea2, 

sum(case when RelatedTabName='C' then Area else 0 end) as MArea3, 

sum(case when RelatedTabName='D' then Area else 0 end) as MArea4, 

--面积(亩)
SUM(Area) as TotalArea
 from (
  select a.ProjectId as ProjectId,a.Village as Village,
  a.TownShip as TownShip,a.CompletedTime as CompletedTime,
  a.ProjectType as ProjectType,a.ManageMan as ManageMan,
  a.IrrigateType as IrrigateType,a.ResourceType as ResourceType,
  a.Area as Area, convert(varchar(50),a.TownShip)+','+convert(varchar(50),a.Village) as 

position,
  a.ManageMode as ManageUnit,b.RelatedTabId as RelatedTabId,
  b.RelatedTabName as RelatedTabName   
  from A_ProjectInfo a 
  left join (
  select RelatedTabId, ProjectId,RelatedTabName from A_ProjectInfo_Relate 
  where 1=1 and  RelatedTabId in (select MachineId from A_Well)
 ) b on a.ProjectId=b.ProjectId where 1=1 
 --查询条件
 --and a.TownShip={0} 
 --and a.Village={0}  
 --and a.ProjectType='{0}' 
 --and a.CompletedTime<='{0}' 
) t group by t.CompletedTime,t.ProjectType order by t.CompletedTime,t.ProjectType