一个简单的SQL 行列转换语句_mssql数据库教程-查字典教程网
一个简单的SQL 行列转换语句
一个简单的SQL 行列转换语句
发布时间:2016-12-29 来源:查字典编辑
摘要:一个简单的SQL行列转换Author:eaglet在数据库开发中经常会遇到行列转换的问题,比如下面的问题,部门,员工和员工类型三张表,我们要...

一个简单的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语句了。这个功能感觉还是不错,不过合计好像用这种方法不太好搞。不知道各位同行有没有什么好办法。

相关阅读
推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
  • 大家都在看
  • 小编推荐
  • 猜你喜欢
  • 最新mssql数据库学习
    热门mssql数据库学习
    编程开发子分类