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.PlanIDWHERE     (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 @[email protected]+' sum(case Category.CategoryName when '''+categoryName+''' then DXPlanInfo.PlanProCount + DXPlanInfo.PlanWorkCount END) as ['+categoryName+'],'from (select distinct categoryName from category where scheduleID=5) as aset @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.PlanIDGROUP BY dbo.Department.DepartmentName, dbo.Schedule.ScheduleName, dbo.Schedule.Year, dbo.DXPlanInfo.remarks'	exec(@sql)

?