例:
表X
NO VALUE X-FACTOR
1 ONE X1
2 TWO X2表Y
NO VALUE Y-TELENT
1 ONE Y1
3 THREE Y3目标:
表Z
NO VALUE X-FACTOR Y-TELENT
1 ONE X1 Y1
2 TWO X2
3 THREE Y3NO是key,求如何写sql查询语句
表X
NO VALUE X-FACTOR
1 ONE X1
2 TWO X2表Y
NO VALUE Y-TELENT
1 ONE Y1
3 THREE Y3目标:
表Z
NO VALUE X-FACTOR Y-TELENT
1 ONE X1 Y1
2 TWO X2
3 THREE Y3NO是key,求如何写sql查询语句
create table TX
([NO] int, value varchar(5), [X-FACTOR] varchar(4))insert into TX
select 1, 'ONE', 'X1' union all
select 2, 'TWO', 'X2'create table TY
([NO] int, value varchar(5), [Y-TELENT] varchar(4))insert into TY
select 1, 'ONE', 'Y1' union all
select 3, 'THREE', 'Y3'
select isnull(x.[NO],y.[NO]) 'NO',
isnull(x.[VALUE],y.[VALUE]) 'VALUE',
isnull(x.[X-FACTOR],'') 'X-FACTOR',
isnull(y.[Y-TELENT],'') 'Y-TELENT'
into TZ
from TX x
full join TY y
on x.[NO]=y.[NO]select * from TZ/*
NO VALUE X-FACTOR Y-TELENT
----------- ----- -------- --------
1 ONE X1 Y1
2 TWO X2
3 THREE Y3(3 row(s) affected)
*/
(
select NO, VALUE
from X
union
select NO, VALUE
from Y
)
select NO,
VALUE,
X.X-FACTOR,
Y.Y-TELENT
from tabletmp
LEFT OUTER JOIN X on X.NO = tabletmp.NO
LEFT OUTER JOIN Y on Y.NO = tabletmp.NO
NO VALUE X-FACTOR Y-TELENT
1 ONE X1 「空」
1 ONE 「空」 Y1
2 TWO X2 「空」
3 THREE 「空」 Y3脑袋里现在是一片浆糊。。