Sql分类统计

通过sql语句实现类似交叉表，分类统计的效果

 

一、类别固定

SELECT     dbo.Department.DepartmentName AS 单位名称, dbo.Schedule.ScheduleName, dbo.Schedule.Year, 
                      SUM(CASE Category.CategoryName WHEN '厂房与建筑' THEN DXPlanInfo.PlanProCount + DXPlanInfo.PlanWorkCount END) AS 厂房与建筑, 
                      SUM(CASE Category.CategoryName WHEN '生活福利' THEN DXPlanInfo.PlanProCount + DXPlanInfo.PlanWorkCount END) AS 生活福利, 
                      SUM(CASE Category.CategoryName WHEN '交通运输' THEN DXPlanInfo.PlanProCount + DXPlanInfo.PlanWorkCount END) AS 交通运输, 
                      SUM(CASE Category.CategoryName WHEN '综采综掘' THEN DXPlanInfo.PlanProCount + DXPlanInfo.PlanWorkCount END) AS 综采综掘, 
                      SUM(CASE Category.CategoryName WHEN '一般设备' THEN DXPlanInfo.PlanProCount + DXPlanInfo.PlanWorkCount END) AS 一般设备, 
                      SUM(CASE Category.CategoryName WHEN '其它' THEN DXPlanInfo.PlanProCount + DXPlanInfo.PlanWorkCount END) AS 其它, 
                      dbo.DXPlanInfo.remarks AS 备注
FROM         dbo.Category INNER JOIN
                      dbo.Schedule INNER JOIN
                      dbo.[Plan] ON dbo.Schedule.ID = dbo.[Plan].ScheduleID INNER JOIN
                      dbo.Department ON dbo.[Plan].DepartmentID = dbo.Department.ID ON dbo.Category.ID = dbo.[Plan].CategoryID INNER JOIN
                      dbo.DXPlanInfo INNER JOIN
                      dbo.Item ON dbo.DXPlanInfo.ItemID = dbo.Item.ID ON dbo.[Plan].ID = dbo.Item.PlanID
WHERE     (dbo.Schedule.ScheduleName = '****')
GROUP BY dbo.Department.DepartmentName, dbo.Schedule.ScheduleName, dbo.Schedule.Year, dbo.DXPlanInfo.remarks

 二、类别不固定

 

declare @sql varchar(8000)

set @sql='select Department.DepartmentName AS 单位名称, '

select @sql=@sql+' sum(case Category.CategoryName when '''+categoryName+''' then DXPlanInfo.PlanProCount + DXPlanInfo.PlanWorkCount END) as ['+categoryName+'],'
from (select distinct categoryName from category where scheduleID=5) as a

set @sql = @sql + ' dbo.DXPlanInfo.remarks AS 备注  from dbo.Category INNER JOIN
                      dbo.Schedule INNER JOIN
                      dbo.[Plan] ON dbo.Schedule.ID = dbo.[Plan].ScheduleID INNER JOIN
                      dbo.Department ON dbo.[Plan].DepartmentID = dbo.Department.ID ON dbo.Category.ID = dbo.[Plan].CategoryID INNER JOIN
                      dbo.DXPlanInfo INNER JOIN
                      dbo.Item ON dbo.DXPlanInfo.ItemID = dbo.Item.ID ON dbo.[Plan].ID = dbo.Item.PlanID
GROUP BY dbo.Department.DepartmentName, dbo.Schedule.ScheduleName, dbo.Schedule.Year, dbo.DXPlanInfo.remarks'
	
exec(@sql)