declare @A0 table(Id int ,K0 int)
declare @A1 table(Id int ,K1 int)
declare @A2 table(Id int ,K2 int)
insert @A0 select 1,100
insert @A1 select 1,267
insert @A2 select 1,''
select a0.ID,a0.K0,a1.K1,a2.K2 from @A0 a0,@A1 a1,@A2 a2 where a0.id=a1.id and a1.id=a2.idID K0 K1 K2
----------- ----------- ----------- -----------
1 100 267 0(1 row(s) affected)
declare @A1 table(Id int ,K1 int)
declare @A2 table(Id int ,K2 int)
insert @A0 select 1,100
insert @A1 select 1,267
insert @A2 select 1,''
select a0.ID,a0.K0,a1.K1,a2.K2 from @A0 a0,@A1 a1,@A2 a2 where a0.id=a1.id and a1.id=a2.idID K0 K1 K2
----------- ----------- ----------- -----------
1 100 267 0(1 row(s) affected)
inner join a1 on a0.id=a1.id
inner join a2 on a1.id=a2.id;
create table a1(id int,k1 int)
create table a2(id int,k2 int)insert into a0 values(1,100)--a0数据
insert into a1 values(1,234)--a1数据
--a3不增加数据,空表--注:3个表,可能1个表有数据,可能2个,可能3个,不确定,上面的数据是测试用
得到的效果
(id,k0,k1,k2)
按照上面的就是(1,100,234,0)
declare @A0 table(Id int ,K0 int)
declare @A1 table(Id int ,K1 int)
declare @A2 table(Id int ,K2 int)
insert @A0 select 1,100
insert @A1 select 1,267 insert @A0 select 2,100 insert @A2 select 2,111
select * from @A0
select * from @A1
select * from @A2select a0.ID,isnull(a0.K0,0) as K0,isnull(a1.K1,0) as K1,isnull(a2.K2,0) as K2
from @A0 a0
full outer join @A1 a1 on a0.id=a1.id
full outer join @A2 a2 on a0.id=a2.id