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