服务器之家

服务器之家 > 正文

一个简单的SQL 行列转换语句

时间:2019-11-14 17:27     来源/作者:mssql教程网

一个简单的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 
create table Department 

Id int primary key , 
Department varchar ( 10 ) 

create table Employee 

EmployeeId int primary key , 
DepartmentId int Foreign Key (DepartmentId) References Department(Id) , -- DepartmentId , 
EmployeeName varchar ( 10 ) 

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 ' ); 
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 ' ); 
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 ' ) 


执行结果如下: 
部门编号 部门名称 辞退 临时 正式 合计 
----------- ---------- ----------- ----------- ----------- ----------- 
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语句了。这个功能感觉还是不错,不过合计好像用这种方法不太好搞。不知道各位同行有没有什么好办法。

标签:

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
配置IIS网站web服务器的安全策略配置解决方案
配置IIS网站web服务器的安全策略配置解决方案 2019-05-23
Nginx服务器究竟是怎么执行PHP项目
Nginx服务器究竟是怎么执行PHP项目 2019-05-24
运维必须知道的关于云服务器的十个问题
运维必须知道的关于云服务器的十个问题 2019-05-24
返回顶部