34,838
社区成员




create table 人员表 (infoid int identity(1,1),name varchar(50),birthday datetime)
insert into 人员表 select 'a',getdate()
insert into 人员表 select 'b',getdate()
insert into 人员表 select 'c',getdate()
insert into 人员表 select 'd',getdate()
insert into 人员表 select 'e',getdate()
create table 证件表 (infoid int,idcardtype int,idcardno varchar(50))
insert into 证件表 select 1,2,'a11'
insert into 证件表 select 1,3,'a22'
insert into 证件表 select 1,4,'a33'
insert into 证件表 select 2,2,'b11'
insert into 证件表 select 2,3,'b22'
insert into 证件表 select 2,4,'b33'
insert into 证件表 select 3,4,'c33'
insert into 证件表 select 4,3,'d22'
insert into 证件表 select 4,4,'d33'
insert into 证件表 select 4,5,'d33'
select t1.name,t1.birthday,idcardtype1,idcardno1,
idcardcardtype2=(select top 1 idcardtype from 证件表 where infoid=T1.infoid and idcardtype<>T2.idcardtype1 order by idcardtype),
idcardno2 =(select top 1 idcardno from 证件表 where infoid=T1.infoid and idcardtype<>T2.idcardtype1 order by idcardtype)
from 人员表 T1
inner join
(select infoid,name,birthday,
idcardtype1=(select top 1 idcardtype from 证件表 where infoid=A.infoid order by case when idcardtype=2 then 1 else 2 end,idcardtype),
idcardno1=(select top 1 idcardno from 证件表 where infoid=A.infoid order by case when idcardtype=2 then 1 else 2 end,idcardtype)
from 人员表 A) T2
on T1.infoid=T2.infoid
/*
a 2008-03-04 10:11:38.340 2 a11 3 a22
b 2008-03-04 10:11:38.340 2 b11 3 b22
c 2008-03-04 10:11:38.340 4 c33 NULL NULL
d 2008-03-04 10:11:38.340 3 d22 4 d33
e 2008-03-04 10:11:38.340 NULL NULL NULL NULL
*/
drop table 人员表,证件表
--試下這個
select name,birthday,idcardtype1,idcardno1,
idcardcardtype2=(select top 1 idcardtype from 证件表 where infoid=T1.infoid and idcardtype<>T2.idcardtype1 order by idcardtype),
idcardno2 =(select top 1 idcardno from 证件表 where infoid=T1.infoid and idcardtype<>T2.idcardtype1 order by idcardtype)
from 人员表 T1
inner join
(select infoid,name,birthday,
idcardtype1=(select top 1 idcardtype from 证件表 where infoid=A.infoid order by case when idcardtype=2 then 1 else 2 end,idcardtype),
idcardno1=(select top 1 idcardno from 证件表 where infoid=A.infoid order by case when idcardtype=2 then 1 else 2 end,idcardtype)
from 人员表 A) T2
on T1.infoid=T2.infoid
create table 人员表 (infoid int identity(1,1),name varchar(50),birthday datetime)
insert into 人员表 select 'a',getdate()
insert into 人员表 select 'b',getdate()
insert into 人员表 select 'c',getdate()
insert into 人员表 select 'd',getdate()
create table 证件表 (infoid int,idcardtype int,idcardno varchar(50))
insert into 证件表 select 1,2,'a11'
insert into 证件表 select 1,3,'a22'
insert into 证件表 select 1,4,'a33'
insert into 证件表 select 2,2,'b11'
insert into 证件表 select 2,3,'b22'
insert into 证件表 select 2,4,'b33'
insert into 证件表 select 3,2,'c11'
insert into 证件表 select 3,3,'c22'
insert into 证件表 select 3,4,'c33'
insert into 证件表 select 4,2,'d11'
insert into 证件表 select 4,3,'d22'
insert into 证件表 select 4,4,'d33'
select name,birthday,
(select top 1 idcardtype from 证件表 where infoid=a.infoid order by case idcardtype when 2 then 0 else 1 end)idcardtype1,
(select top 1 idcardno from 证件表 where infoid=a.infoid order by case idcardtype when 2 then 0 else 1 end)idcardno1,
(select top 1 idcardtype from 证件表 where infoid=a.infoid order by case idcardtype when 2 then 1 else 0 end)idcardtype2,
(select top 1 idcardtype from 证件表 where infoid=a.infoid order by case idcardtype when 2 then 1 else 0 end)idcardno2
from 人员表 a