表结构这样的:
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
总是不成功,求解
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
总是不成功,求解
Y
----
1.a
2.b
3.c
4.dselect *
from a
right outer join b on a.Y = b.Y
order by a.X, b.Y
不过这么做得不到正确结果
select *
from a
right outer join b on (a.X+a.Y) = (a.X+b.Y)
order by a.X,b.Y
不过也不行,没辙了,不知道怎么写才好
(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
*/
drop table tb
gocreate 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.ydrop 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 行)
*/
insert @t 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',3select t3.*,t.z from @t t full join
(select distinct * from (select X from @t)t1,(Select Y from @t)t2) t3
on t.X=t3.X and t.Y=t3.Y order by t3.X,t3.y