27,582
社区成员




CREATE TABLE [dbo].[T1](
[No] [varchar](50) NULL,
[Item1] [varchar](50) NULL,
[Item2] [varchar](50) NULL
)
CREATE TABLE [dbo].[T2](
[No] [varchar](50) NULL,
[Item5] [varchar](50) NULL,
[Item6] [varchar](50) NULL
)
CREATE TABLE [dbo].[T3](
[No] [varchar](50) NULL,
[Item7] [varchar](50) NULL,
[Item8] [varchar](50) NULL
)
insert into T1 (No,Item1,Item2) values ('A0001','A1','B1')
insert into T1 (No,Item1,Item2) values ('A0001','A2','B2')
insert into T1 (No,Item1,Item2) values ('A0001','A3','B3')
insert into T1 (No,Item1,Item2) values ('A0002','A4','')
insert into T1 (No,Item1,Item2) values ('A0002','A5','')
insert into T2 (No,Item5,Item6) values ('A0001','C1','D1')
insert into T2 (No,Item5,Item6) values ('A0001','C2','D2')
insert into T2 (No,Item5,Item6) values ('A0001','C3','D3')
insert into T2 (No,Item5,Item6) values ('A0001','C4','D4')
insert into T2 (No,Item5,Item6) values ('A0001','C5','D5')
insert into T2 (No,Item5,Item6) values ('A0001','C6','D6')
insert into T3 (No,Item7,Item8) values ('A0001','E1','F1')
insert into T3 (No,Item7,Item8) values ('A0001','E2','F2')
insert into T3 (No,Item7,Item8) values ('A0001','E3','F3')
insert into T3 (No,Item7,Item8) values ('A0001','E4','F4')
insert into T3 (No,Item7,Item8) values ('A0002','E5','F5')
insert into T3 (No,Item7,Item8) values ('A0002','E6','F6')
insert into T3 (No,Item7,Item8) values ('A0002','E6','F6')
select coalesce(a.No,b.No,c.No)No,
isnull(a.item1,'')item1,
isnull(a.item2,'')item2,
isnull(b.item5,'')item5,
isnull(b.item6,'')item6,
isnull(c.item7,'')item7,
isnull(item8,'')item8
from (select row_number()over(Partition by No order by No asc) as Rid,* from T1
) a
full join(
select row_number()over(partition by No order by No asc) as Rid,* from T2
) b on a.Rid=b.Rid and a.No=b.No
full join (
select row_number()over(partition by No order by No asc) as Rid,* from T3
) c on isnull(a.Rid,b.Rid)=c.Rid and isnull(a.No,b.No)=c.No
go