我试了 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
都不行啊,难道要先求出最长的表?
...全文
4499打赏收藏
多表full join时怎么写ON?
每个表都有自增列作为连接条件,但行数不等,连出的效果要求中间没空行。如 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 都不行啊,难道要先求出最长的表?
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
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
再直接一点:
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)
)
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
第三列有空行
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