快捷導(dǎo)航

一個(gè)簡(jiǎn)單的SQL 行列轉(zhuǎn)換語(yǔ)句

更新時(shí)間：2009年08月29日 16:35:36 作者：

在數(shù)據(jù)庫(kù)開(kāi)發(fā)中經(jīng)常會(huì)遇到行列轉(zhuǎn)換的問(wèn)題，比如下面的問(wèn)題，部門(mén)，員工和員工類(lèi)型三張表，我們要統(tǒng)計(jì)類(lèi)似這樣的列表

一個(gè)簡(jiǎn)單的SQL 行列轉(zhuǎn)換
Author: eaglet
在數(shù)據(jù)庫(kù)開(kāi)發(fā)中經(jīng)常會(huì)遇到行列轉(zhuǎn)換的問(wèn)題，比如下面的問(wèn)題，部門(mén)，員工和員工類(lèi)型三張表，我們要統(tǒng)計(jì)類(lèi)似這樣的列表
部門(mén)編號(hào) 部門(mén)名稱(chēng) 合計(jì) 正式員工臨時(shí)員工辭退員工
1 A 30 20 10 1
這種問(wèn)題咋一看摸不著頭緒，不過(guò)把思路理順后再看，本質(zhì)就是一個(gè)行列轉(zhuǎn)換的問(wèn)題。下面我結(jié)合這個(gè)簡(jiǎn)單的例子來(lái)實(shí)現(xiàn)行列轉(zhuǎn)換。
下面3張表

復(fù)制代碼代碼如下:

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

描述部門(mén)，員工和員工類(lèi)型之間的關(guān)系。
插入測(cè)試數(shù)據(jù)

復(fù)制代碼代碼如下:

 
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 , ' 臨時(shí) ' ); 
insert EmployeeType values ( 3 , ' 正式 ' ); 
insert EmployeeType values ( 4 , ' 正式 ' ); 
insert EmployeeType values ( 5 , ' 辭退 ' ); 
insert EmployeeType values ( 6 , ' 正式 ' ); 

看一下部門(mén)、員工和員工類(lèi)型的列表
Department EmployeeName EmployeeType
---------- ------------ ------------
A Bob 正式
A John 臨時(shí)
A May 正式
B Tom 正式
B Mark 辭退
B Ken 正式
現(xiàn)在我們需要輸出這樣一個(gè)列表
部門(mén)編號(hào) 部門(mén)名稱(chēng) 合計(jì) 正式員工臨時(shí)員工辭退員工
這個(gè)問(wèn)題我的思路是首先統(tǒng)計(jì)每個(gè)部門(mén)的員工類(lèi)型總數(shù)
這個(gè)比較簡(jiǎn)單，我把它做成一個(gè)視圖

復(fù)制代碼代碼如下:

 
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 

現(xiàn)在 select * from VDepartmentEmployeeType
Id Department EmployeeType Cnt
----------- ---------- ------------ -----------
2 B 辭退 1
1 A 臨時(shí) 1
1 A 正式 2
2 B 正式 2
有了這個(gè)結(jié)果，我們?cè)偻ㄟ^(guò)行列轉(zhuǎn)換，就可以實(shí)現(xiàn)要求的輸出了
行列轉(zhuǎn)換采用 case 分支語(yǔ)句來(lái)實(shí)現(xiàn)，如下：

復(fù)制代碼代碼如下:

 
select Id as ' 部門(mén)編號(hào) ' , Department as ' 部門(mén)名稱(chēng) ' , 
[ 正式 ] = Sum ( case when EmployeeType = ' 正式 ' then Cnt else 0 end ), 
[ 臨時(shí) ] = Sum ( case when EmployeeType = ' 臨時(shí) ' then Cnt else 0 end ), 
[ 辭退 ] = Sum ( case when EmployeeType = ' 辭退 ' then Cnt else 0 end ), 
[ 合計(jì) ] = Sum ( case when EmployeeType <> '' then Cnt else 0 end ) 
from VDepartmentEmployeeType 
GROUP BY Id, Department 

看一下結(jié)果
部門(mén)編號(hào) 部門(mén)名稱(chēng) 正式臨時(shí) 辭退合計(jì)
----------- ---------- ----------- ----------- ----------- -----------
1 A 2 1 0 3
2 B 2 0 1 3
現(xiàn)在還有一個(gè)問(wèn)題，如果員工類(lèi)型不可以應(yīng)編碼怎么辦？也就是說(shuō)我們?cè)趯?xiě)程序的時(shí)候并不知道有哪些員工類(lèi)型。這確實(shí)是一個(gè)
比較棘手的問(wèn)題，不過(guò)不是不能解決，我們可以通過(guò)拼接SQL的方式來(lái)解決這個(gè)問(wèn)題?？聪旅娲a

復(fù)制代碼代碼如下:

 
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 部門(mén)編號(hào), Department as 部門(mén)名稱(chēng), ' + @s + 
' ,[合計(jì)]= Sum(case when EmployeeType <> '''' then Cnt else 0 end) ' + 
' from VDepartmentEmployeeType GROUP BY Id, Department ' ) 

執(zhí)行結(jié)果如下：
部門(mén)編號(hào) 部門(mén)名稱(chēng) 辭退臨時(shí) 正式合計(jì)
----------- ---------- ----------- ----------- ----------- -----------
1 A 0 1 2 3
2 B 1 0 2 3
這個(gè)結(jié)果和前面硬編碼的結(jié)果是一樣的，但我們通過(guò)程序來(lái)獲取了所有的員工類(lèi)型，這樣做的好處是如果我們新增了一個(gè)員工類(lèi)型，比如“合同工”，我們不需要修改程序，就可以得到我們想要的輸出。

如果你的數(shù)據(jù)庫(kù)是SQLSERVER 2005 或以上，也可以采用SQLSERVER2005 通過(guò)的新功能 PIVOT

復(fù)制代碼代碼如下:

 
SELECT Id as ' 部門(mén)編號(hào) ' , Department as ' 部門(mén)名稱(chēng) ' , [ 正式 ] , [ 臨時(shí) ] , [ 辭退 ] 
FROM 
( SELECT Id,Department,EmployeeType,Cnt 
FROM VDepartmentEmployeeType) p 
PIVOT 
( SUM (Cnt) 
FOR EmployeeType IN ( [ 正式 ] , [ 臨時(shí) ] , [ 辭退 ] ) 
) AS unpvt 

結(jié)果如下
部門(mén)編號(hào) 部門(mén)名稱(chēng) 正式臨時(shí) 辭退
----------- ---------- ----------- ----------- -----------
1 A 2 1 NULL
2 B 2 NULL 1
NULL 可以通過(guò) ISNULL 函數(shù)來(lái)強(qiáng)制轉(zhuǎn)換為0，這里我就不寫(xiě)出具體的SQL語(yǔ)句了。這個(gè)功能感覺(jué)還是不錯(cuò)，不過(guò)合計(jì)好像用這種方法不太好搞。不知道各位同行有沒(méi)有什么好辦法。

您可能感興趣的文章: