select [year]=isnull(isnull(#temp1.[year],#temp2.[year]),#temp3.[year]),
#temp1.a,#temp2.b,#temp3.c
from #temp1 full join #temp2 on #temp1.[year]=#temp2.[year]
full join #temp3 on #temp1.[year]=#temp3.[year]
#temp1.a,#temp2.b,#temp3.c
from #temp1 full join #temp2 on #temp1.[year]=#temp2.[year]
full join #temp3 on #temp1.[year]=#temp3.[year]
#temp1.a,#temp2.b,#temp3.c
from #temp1 full join #temp2 on #temp1.[year]=#temp2.[year]
full join #temp3 on #temp1.[year]=#temp3.[year]
drop table #temp1,#temp2,#temp3
if not exists(select * from #temp1)
begin
-----进行连接操作
end
select top 1 [year]=2005,b=80 into #temp2 from sysobjects
select top 0 [year]=2005,c=60 into #temp3 from sysobjectsselect [year]=isnull(isnull(#temp1.[year],#temp2.[year]),#temp3.[year]),
#temp1.a,#temp2.b,#temp3.c
from #temp1 full join #temp2 on #temp1.[year]=#temp2.[year]
full join #temp3 on #temp1.[year]=#temp3.[year]
drop table #temp1,#temp2,#temp3返回:
2005 100 80 NULL
select top 0 [year]=2005,a=100 into #temp1 from sysobjects
select top 1 [year]=2005,b=80 into #temp2 from sysobjects
select top 1 [year]=2005,c=60 into #temp3 from sysobjectsselect [year]=isnull(isnull(#temp1.[year],#temp2.[year]),#temp3.[year]),
#temp1.a,#temp2.b,#temp3.c
from #temp1 full join #temp2 on #temp1.[year]=#temp2.[year]
full join #temp3 on isnull(#temp1.[year],#temp2.[year])=#temp3.[year]--此处条件改了一下drop table #temp1,#temp2,#temp3
Create Table #temp1
([year] Int,
a Int)Create Table #temp2
([year] Int,
b Int)Create Table #temp3
([year] Int,
c Int)
--测试
--#temp1、#temp2有数据
Insert #temp1 Values(2005,10)
Insert #temp2 Values(2005,80)Select
IsNull(IsNull(A.[year],B.[year]),C.[year]) As [year],
A.a,
B.b,
C.c
from #temp1 A
Full Join #temp2 B On A.[year]=B.[year]
Full Join #temp3 C On IsNull(A.[year],B.[year])=C.[year]--#temp2、#temp3有数据
Delete from #temp1
Delete from #temp2
Insert #temp2 Values(2005,10)
Insert #temp3 Values(2005,80)Select
IsNull(IsNull(A.[year],B.[year]),C.[year]) As [year],
A.a,
B.b,
C.c
from #temp1 A
Full Join #temp2 B On A.[year]=B.[year]
Full Join #temp3 C On IsNull(A.[year],B.[year])=C.[year]--#temp1、#temp3有数据
Delete from #temp2
Delete from #temp3
Insert #temp1 Values(2005,10)
Insert #temp3 Values(2005,80)Select
IsNull(IsNull(A.[year],B.[year]),C.[year]) As [year],
A.a,
B.b,
C.c
from #temp1 A
Full Join #temp2 B On A.[year]=B.[year]
Full Join #temp3 C On IsNull(A.[year],B.[year])=C.[year]
--删除测试环境
Drop Table #temp1,#temp2,#temp3
--结果
/*
--#temp1、#temp2有数据
year a b c
2005 10 80 NULL
--#temp2、#temp3有数据
year a b c
2005 NULL 10 80--#temp1、#temp3有数据
year a b c
2005 10 NULL 80
*/
我想了一个办法, 先自己create一个temp0 一行一列 year 2005然后用temp0 left join temp1,temp2......这样应该没有问题把ps:我在存储过程里创造的#temp 最后必须drop table #temp 么?我都没有做哦
可以不需要的。如果表都有数据的话,那就简单了。没有的话就照上面那么处理。
如果temp1空, 那么就不行了所以我想先做个temp0 强制插一行2005 这样 一路往下 left join 就应该没问题了,就算遇到 temp(n) 是空的也就是补个NUll就行了, 不知道我的想法对不对
year a year b year c
2005 100 2005 80 2005 60create table #temp(year int null,a int null,b int null,c int null)
goinsert into #temp(year,a)
select * from #temp1
go
insert into #temp(year,b)
select * from #temp2
go
insert into #temp(year,c)
select * from #temp3
goselect year,max(a) a ,max(b) b ,max(c) c from #temp group by year
go
drop table #tempif exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#temp1'))
drop table #temp1if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#temp2'))
drop table #temp2if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#temp3'))
drop table #temp3
gocreate table #temp(year int null,a int null,b int null,c int null)
create table #temp1(year int null,a int null)
create table #temp2(year int null,b int null)
create table #temp3(year int null,c int null)
go
insert into #temp1
select 2005,100insert into #temp2
select 2005,80goselect * from #temp1
/*
year a
2005 100
*/
select * from #temp2
/*
year b
2005 80
*/
select * from #temp3
/*
year c
*/
insert into #temp(year,a)
select * from #temp1insert into #temp(year,b)
select * from #temp2insert into #temp(year,c)
select * from #temp3
goselect year,max(a) a ,max(b) b ,max(c) c from #temp group by year
/*
year a b c
2005 100 80 NULL
*/
drop table #temp1if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#temp2'))
drop table #temp2if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#temp3'))
drop table #temp3
gocreate table #temp1(year int null,a int null)
create table #temp2(year int null,b int null)
create table #temp3(year int null,c int null)
go
insert into #temp2
select 2005,80insert into #temp3
select 2005,100goselect * from #temp1
/*
year a
*/
select * from #temp2
/*
year b
2005 80
*/
select * from #temp3
/*
year c
2005 100
*/
select year,max(a)a,max(b)b,max(c) c from
(
select year,a,NULL b,NULL c from #temp1 union all
select year,NULL a,b,NULL c from #temp2 union all
select year,NULL a,NULL b,c from #temp3
)a group by year
/*
year a b c
2005 NULL 80 100
*/