多表full join时怎么写ON？

[denxiaom]

每个表都有自增列作为连接条件，但行数不等，连出的效果要求中间没空行。如

1 2 3
a b c
d e f
g h
i

我试了 t1 full join t2 on t1.id=t2.id full join on t1.id=t3.id和
t1 full join t2 on t1.id=t2.id full join on t2.id=t3.id
都不行啊，难道要先求出最长的表？

[vivianfdlpw]

declare @a table
(
id int identity,col varchar(10)
)
declare @b table
(
id int identity,col varchar(10)
)
declare @c table
(
id int identity,col varchar(10)
)
insert @a(col)
select 'a' union select 'd' union select 'e'
insert @b(col)
select 'b' union select 'e'
insert @c(col)
select 'c' union select 'f' union select 'h' union select 'i'

--查询
select a.col,b.col,c.col
from @a a
full join @b b on a.id=b.id
full join @c c on a.id=c.id

--结果
/*
col col col
---------- ---------- ----------
a b c
d e f
e NULL h
NULL NULL i

(4 row(s) affected)
*/

[僵哥]

楼主咋连个问题都叙述不清楚？

[iwl]

什么意思 你的表结构是什么

[denxiaom]

不行 ：(
declare @a table
(
id int identity,col varchar(10)
)
declare @b table
(
id int identity,col varchar(10)
)
declare @c table
(
id int identity,col varchar(10)
)
insert @a(col)
select 'a' union select 'd' union select 'e'
insert @b(col)
select 'b' union select 'e' union select '1' union select '2'
insert @c(col)
select 'c' union select 'f' union select 'h' union select 'i' union select '3'

--查询
select a.col,b.col,c.col
from @a a
full join @b b on a.id=b.id
full join @c c on a.id=c.id

--结果
a 1 3
d 2 c
e b f
NULL e NULL
NULL NULL i
NULL NULL h
第三列有空行

[denxiaom]

意思就是多张完全不相关的表连起来。

[denxiaom]

再直接一点：
declare @a table
(
id int identity,col varchar(10)
)
declare @b table
(
id int identity,col varchar(10)
)
declare @c table
(
id int identity,col varchar(10)
)
declare @d table
(
id int identity,col varchar(10)
)
insert @a(col)
select 'a' union select 'd' union select 'e'
insert @b(col)
select 'b' union select 'e' union select '1' union select '2'
insert @c(col)
select 'c' union select 'f' --union select 'h' union select 'i' union select '3'
insert @d(col)
select 'c' union select 'f' union select 'h' union select 'i' union select '3'
把这4个表做出
a b c c
d e f f
e 1 h
2 i
3
的效果。

[僵哥]

declare @a table
(
id int identity,col varchar(10)
)
declare @b table
(
id int identity,col varchar(10)
)
declare @c table
(
id int identity,col varchar(10)
)
declare @d table
(
id int identity,col varchar(10)
)
insert @a(col)
select 'a' union select 'd' union select 'e'
insert @b(col)
select 'b' union select 'e' union select '1' union select '2'
insert @c(col)
select 'c' union select 'f' --union select 'h' union select 'i' union select '3'
insert @d(col)
select 'c' union select 'f' union select 'h' union select 'i' union select '3'
--
select a.col,b.col,c.col,d.col
from @a a
full join @b b on a.id=b.id
full join @c c on isnull(a.id,b.id)=c.id
full join @d d on isnull(a.id,isnull(b.id,c.id))=d.id

--结果
col col col col
-------------------------------
a 1 c 3
d 2 f c
e b NULL f
NULL e NULL h
NULL NULL NULL i

[vivianfdlpw]

declare @a table
(
id int identity,col varchar(10)
)
declare @b table
(
id int identity,col varchar(10)
)
declare @c table
(
id int identity,col varchar(10)
)
declare @d table
(
id int identity,col varchar(10)
)
insert @a(col)
select 'a' union select 'd' union select 'e'
insert @b(col)
select 'b' union select 'e' union select '1' union select '2'
insert @c(col)
select 'c' union select 'f'
insert @d(col)
select 'c' union select 'f' union select 'h' union select 'i' union select '3'

--查询
select top 1000 identity(int,1,1) id into # from syscolumns a,syscolumns b

select a.col
,b.col
,c.col
,d.col
from # t
full join @a a on t.id=a.id
full join @b b on t.id=b.id
full join @c c on t.id=c.id
full join @d d on t.id=d.id
where coalesce(a.col,b.col,c.col,d.col) is not null

drop table #

--结果
/*
col col col col
---------- ---------- ---------- ----------
a 1 c 3
d 2 f c
e b NULL f
NULL e NULL h
NULL NULL NULL i

(5 row(s) affected)
*/

[denxiaom]

3Q! :)