行列变换

 

假设有如下表，记录着员工的电话联系方式。

PhoneType ： 0,1,2 分别代表着手机号码，家里固话号码，公司电话号码

Create table EmpInfo
(    EmpID int,
    PhoneNum varchar(15),
    PhoneType tinyint)

 

现在需要一个如下的查询结果

EmpID, TeleNum，HomeNum，CompanyNum

 

SQL 该如何写？

这是一个典型的行列变换的题目。 SQL 如下

 

insert into EmpInfo values(1,'13811569871',0)
insert into EmpInfo values(1,'010-62289087',1)
insert into EmpInfo values(1,'010-71172345',2)

insert into EmpInfo values(2,'13833871256',0)
insert into EmpInfo values(2,'0375-10099087',1)
insert into EmpInfo values(2,'010-71172345',2)

insert into EmpInfo values(3,'13899998888',0)

insert into EmpInfo values(4,'0372-66669087',1)
insert into EmpInfo values(4,'010-71172345',2)

 

 

select M.EmpID, T.PhoneNum as TeleNo, H.PhoneNum as HomeNum, P.PhoneNum as CompanyNum
from    (select distinct EmpID from EmpInfo) M
    left join  (select EmpID,PhoneNum  from EmpInfo where PhoneType= 0) T  on M.EmpID = T.EmpID
    left join (select EmpID,PhoneNum from EmpInfo where PhoneType= 1) H on M.EmpID = H.EmpID
    left join (select EmpID,PhoneNum from EmpInfo where PhoneType= 2) P on M.EmpID = P.EmpID

 

结果如下：

 

考虑不使用Join,而在where中使用EmpID相等行不，如下：

select M.EmpID, T.PhoneNum as TeleNo, H.PhoneNum as HomeNum, P.PhoneNum as CompanyNum
from (select EmpID,PhoneNum from EmpInfo where PhoneType= 0) T,
     (select EmpID,PhoneNum from EmpInfo where PhoneType= 1) H,
     (select EmpID,PhoneNum from EmpInfo where PhoneType= 2) P,
     (select distinct EmpID from EmpInfo ) M
where M.EmpID = T.EmpID
and M.EmpID = H.EmpID
and M.EmpID = P.EmpID

 

 

结果显然是不对的。 比如当有人没有手机号码的情况