假设有如下表,记录着员工的电话联系方式。
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
结果显然是不对的。 比如当有人没有手机号码的情况