看似简单的outer join 问题，但我就是没法得到正确结果
表结构这样的：
XYZ
--------------------
w1.a2
w2.b3
w3.c3
w4.d6
u2.b8
u4.d6
v2.b7
v3.c18
v4.d3
我想得到这样的输出结果：
XYZ
-------------------------
w1.a2
w2.b3
w3.c3
w4.d6
u1.anull
u2.b8
u3.cnull
u4.d6
v1.anull
v2.b7
v3.c18
v4.d3
总是不成功，求解
[解决办法]
Create Table TEST
(XVarchar(10),
YVarchar(10),
ZInt)
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
/*
XYZ
u1.aNULL
u2.b8
u3.cNULL
u4.d6
v1.aNULL
v2.b7
v3.c18
v4.d3
w1.a2
w2.b3
w3.c3
w4.d6
*/
[解决办法]
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 行）
*/
