CREATE TABLE EmployeeType
(
ID INT PRIMARY KEY,
strEmployeeType VARCHAR(40) NOT NULL
)
go
insert into EmployeeType(ID,strEmployeeType)
select 1,'管理类' union
select 2,'业务类' union
select 3,'技术类' union
select 4,'文职类'
go
CREATE TABLE Employee
(
strEmployeeCode VARCHAR(10) PRIMARY KEY,
strEmployeeName VARCHAR(40) NOT NULL,
strDepartment VARCHAR(20) NOT NULL,
strEmployeeType VARCHAR(40) NOT NULL
)
go
insert into EmployeeType(strEmployeeCode,strEmployeeName,strDepartment,strEmployeeType)
select '001','张三','信息中心','管理类' union
select '002','李四','信息中心','管理类' union
select '003','王五','行政部','文职类' union
select '004','唯一','信息中心','技术类'
GO
按部门和员工类别统计员工人数，以部门为行，员工类别为列，人员类别中的：取 EmployeeType表的信息内容，随着 EmployeeType表信息的变动而变动。
查得到如何结果
------解决思路----------------------
--动态
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT strDepartment'
SELECT @SQL=@SQL+',SUM(CASE WHEN strEmployeeType='''+strEmployeeType+''' THEN 1 ELSE 0 END)['+strEmployeeType+']'
FROM EmployeeType GROUP BY strEmployeeType,ID ORDER BY ID
SET @SQL=@SQL+'FROM Employee GROUP BY strDepartment'
--PRINT @SQL
EXEC(@SQL)
--静态
SELECT strDepartment
,SUM(CASE WHEN strEmployeeType='管理类' THEN 1 ELSE 0 END)[管理类]
,SUM(CASE WHEN strEmployeeType='业务类' THEN 1 ELSE 0 END)[业务类]
,SUM(CASE WHEN strEmployeeType='技术类' THEN 1 ELSE 0 END)[技术类]
,SUM(CASE WHEN strEmployeeType='文职类' THEN 1 ELSE 0 END)[文职类]
FROM Employee
GROUP BY strDepartment
------解决思路----------------------
declare @sql varchar(max)
set @sql=''
select @sql=@sql + ',['+rtrim(a.strEmployeeType)+']=sum(case strEmployeeType when '''+rtrim(a.strEmployeeType)+''' then 1 else 0 end)'
from EmployeeType a left join Employee b on a.strEmployeeType=b.strEmployeeType group by a.strEmployeeType
exec ('select strDepartment '+@sql+'from Employee group by strDepartment' )
/*
strDepartment 管理类 技术类 文职类 业务类
-------------------- ----------- ----------- ----------- -----------
信息中心 2 1 0 0
行政部 0 0 1 0
(2 行受影响)
*/