请问下有这SQL语句怎写？？？

[qq_28939113]

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

实现将表1、表2、表3 数据组合成表4，有高手帮忙下？谢谢

[qq_28939113]

select COALESCE(a.No,b.No,c.No), a.Item1,a.Item2, b.Item5,b.Item6, c.Item7,c.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

[八月十八]

[quote=引用 1 楼 qq_28939113 的回复:]
握草 楼上正解，学习了

[morliz子轩]

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