通过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)