在数据库开发中经常会遇到行列转换的问题,比如下面的问题,部门,员工和员工类型三张表,我们要统计类似这样的列表
部门编号 部门名称 合计 正式员工 临时员工 辞退员工
1 A 30 20 10 1
这种问题咋一看摸不着头绪,不过把思路理顺后再看,本质就是一个行列转换的问题。下面我结合这个简单的例子来实现行列转换。
一个简单的SQL 行列转换
Author: eaglet
在数据库开发中经常会遇到行列转换的问题,比如下面的问题,部门,员工和员工类型三张表,我们要统计类似这样的列表
部门编号 部门名称 合计 正式员工 临时员工 辞退员工
1 A 30 20 10 1
这种问题咋一看摸不着头绪,不过把思路理顺后再看,本质就是一个行列转换的问题。下面我结合这个简单的例子来实现行列转换。
下面3张表
if exists (select * from sysobjects where id = object_id('EmployeeType') and type = 'u')
drop table EmployeeType
GO
if exists (select * from sysobjects where id = object_id('Employee') and type = 'u')
drop table Employee
GO
if exists (select * from sysobjects where id = object_id('Department') and type = 'u')
drop table Department
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create table Department
(
Id int primary key,
Department varchar(10)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create table Employee
(
EmployeeId int primary key,
DepartmentId int Foreign Key (DepartmentId) References Department(Id) , --DepartmentId ,
EmployeeName varchar(10)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create table EmployeeType
(
EmployeeId int Foreign Key (EmployeeId) References Employee(EmployeeId) , --EmployeeId ,
EmployeeType varchar(10)
)描述部门,员工和员工类型之间的关系。
插入测试数据
insert Department values (1, 'A');
insert Department values (2, 'B');
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
insert Employee values (1, 1, 'Bob');
insert Employee values (2, 1, 'John');
insert Employee values (3, 1, 'May');
insert Employee values (4, 2, 'Tom');
insert Employee values (5, 2, 'Mark');
insert Employee values (6, 2, 'Ken');
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
insert EmployeeType values (1, '正式');
insert EmployeeType values (2, '临时');
insert EmployeeType values (3, '正式');
insert EmployeeType values (4, '正式');
insert EmployeeType values (5, '辞退');
insert EmployeeType values (6, '正式');看一下部门、员工和员工类型的列表
Department EmployeeName EmployeeType
---------- ------------ ------------
A Bob 正式
A John 临时
A May 正式
B Tom 正式
B Mark 辞退
B Ken 正式
现在我们需要输出这样一个列表
部门编号 部门名称 合计 正式员工 临时员工 辞退员工
这个问题我的思路是首先统计每个部门的员工类型总数
这个比较简单,我把它做成一个视图
if exists (select * from sysobjects where id = object_id('VDepartmentEmployeeType') and type = 'v')
drop view VDepartmentEmployeeType
GO
create view VDepartmentEmployeeType
as
select Department.Id, Department.Department, EmployeeType.EmployeeType, count(EmployeeType.EmployeeType) Cnt
from Department, Employee, EmployeeType where
Department.Id = Employee.DepartmentId and Employee.EmployeeId = EmployeeType.EmployeeId
group by Department.Id, Department.Department, EmployeeType.EmployeeType
GO现在 select * from VDepartmentEmployeeType
Id Department EmployeeType Cnt
----------- ---------- ------------ -----------
2 B 辞退 1
1 A 临时 1
1 A 正式 2
2 B 正式 2
有了这个结果,我们再通过行列转换,就可以实现要求的输出了
行列转换采用 case 分支语句来实现,如下:
select Id as '部门编号', Department as '部门名称',
[正式]= Sum(case when EmployeeType = '正式' then Cnt else 0 end),
[临时]= Sum(case when EmployeeType = '临时' then Cnt else 0 end),
[辞退]= Sum(case when EmployeeType = '辞退' then Cnt else 0 end),
[合计]= Sum(case when EmployeeType <> '' then Cnt else 0 end)
from VDepartmentEmployeeType
GROUP BY Id, Department看一下结果
部门编号 部门名称 正式 临时 辞退 合计
----------- ---------- ----------- ----------- ----------- -----------
1 A 2 1 0 3
2 B 2 0 1 3
现在还有一个问题,如果员工类型不可以应编码怎么办?也就是说我们在写程序的时候并不知道有哪些员工类型。这确实是一个
比较棘手的问题,不过不是不能解决,我们可以通过拼接SQL的方式来解决这个问题。看下面代码
DECLARE
@s VARCHAR(max)
SELECT @s = isnull(@s + ',','')+ '['+ltrim(EmployeeType)+'] = ' +
'Sum(case when EmployeeType = ''' +
EmployeeType + ''' then Cnt else 0 end)'
FROM (SELECT DISTINCT EmployeeType FROM VDepartmentEmployeeType ) temp
EXEC('select Id as 部门编号, Department as 部门名称,' + @s +
',[合计]= Sum(case when EmployeeType <> '''' then Cnt else 0 end)' +
'from VDepartmentEmployeeType GROUP BY Id, Department')
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
执行结果如下:
部门编号 部门名称 辞退 临时 正式 合计
----------- ---------- ----------- ----------- ----------- -----------
1 A 0 1 2 3
2 B 1 0 2 3
这个结果和前面硬编码的结果是一样的,但我们通过程序来获取了所有的员工类型,这样做的好处是如果我们新增了一个员工类型,比如“合同工”,我们不需要修改程序,就可以得到我们想要的输出。
如果你的数据库是SQLSERVER 2005 或以上,也可以采用SQLSERVER2005 通过的新功能 PIVOT
SELECT Id as '部门编号', Department as '部门名称', [正式],[临时],[辞退]
FROM
(SELECT Id,Department,EmployeeType,Cnt
FROM VDepartmentEmployeeType) p
PIVOT
( SUM (Cnt)
FOR EmployeeType IN ([正式],[临时],[辞退])
)AS unpvt结果如下
部门编号 部门名称 正式 临时 辞退
----------- ---------- ----------- ----------- -----------
1 A 2 1 NULL
2 B 2 NULL 1
NULL 可以通过 ISNULL 函数来强制转换为0,这里我就不写出具体的SQL语句了。这个功能感觉还是不错,不过合计好像用这种方法不太好搞。不知道各位同行有没有什么好办法。