看似简单的outer join 问题，但我就是没法得到正确结果
表结构这样的：
X Y Z
--------------------
w 1.a 2
w 2.b 3
w 3.c 3
w 4.d 6
u 2.b 8
u 4.d 6
v 2.b 7
v 3.c 18
v 4.d 3
我想得到这样的输出结果：
X Y Z
-------------------------
w 1.a 2
w 2.b 3
w 3.c 3
w 4.d 6
u 1.a null
u 2.b 8
u 3.c null
u 4.d 6
v 1.a null
v 2.b 7
v 3.c 18
v 4.d 3
总是不成功，求解

------解决方案--------------------
Create Table TEST
(X Varchar(10),
Y Varchar(10),
Z Int)
Insert TEST Select 'w ', '1.a ', 2
Union All Select 'w ', '2.b ', 3
Union All Select 'w ', '3.c ', 3
Union All Select 'w ', '4.d ', 6
Union All Select 'u ', '2.b ', 8
Union All Select 'u ', '4.d ', 6
Union All Select 'v ', '2.b ', 7
Union All Select 'v ', '3.c ', 18
Union All Select 'v ', '4.d ', 3
GO
Select
T1.*,
T2.Z
From
(Select *From
(Select Distinct X From TEST) A
Cross Join
(Select Distinct Y From TEST) B
) T1
Left Join
TEST T2
On T1.X = T2.X And T1.Y = T2.Y
Order By T1.X, T1.Y
GO
Drop Table TEST
--Result
/*
X Y Z
u 1.a NULL
u 2.b 8
u 3.c NULL
u 4.d 6
v 1.a NULL
v 2.b 7
v 3.c 18
v 4.d 3
w 1.a 2
w 2.b 3
w 3.c 3
w 4.d 6
*/
------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb(X varchar(10),Y varchar(10),Z int)
insert into tb(X,Y,Z) values( 'w ', '1.a ', 2)
insert into tb(X,Y,Z) values( 'w ', '2.b ', 3)
insert into tb(X,Y,Z) values( 'w ', '3.c ', 3)
insert into tb(X,Y,Z) values( 'w ', '4.d ', 6)
insert into tb(X,Y,Z) values( 'u ', '2.b ', 8)
insert into tb(X,Y,Z) values( 'u ', '4.d ', 6)
insert into tb(X,Y,Z) values( 'v ', '2.b ', 7)
insert into tb(X,Y,Z) values( 'v ', '3.c ', 18)
insert into tb(X,Y,Z) values( 'v ', '4.d ', 3)

select * from tb where x = 'w '
union all
select isnull(t2.x, 'u ') x,isnull(t2.y,t1.y) y,isnull(t2.z,null) z from
(
select * from tb where x = 'w '
) t1
left join
(
select * from tb where x = 'u '
) t2
on t1.y = t2.y
union all
select isnull(t2.x, 'v ') x,isnull(t2.y,t1.y) y,isnull(t2.z,null) z from
(
select * from tb where x = 'w '
) t1
left join
(
select * from tb where x = 'v '
) t2
on t1.y = t2.y

drop table tb

/*
X Y Z
---------- ---------- -----------
w 1.a 2
w 2.b 3
w 3.c 3
w 4.d 6
u 1.a NULL
u 2.b 8
u 3.c NULL
u 4.d 6
v 1.a NULL
v 2.b 7
v 3.c 18
v 4.d 3

（所影响的行数为 12 行）
*/