try:
select a.YID,A.MID,A.num1,b.num2,c.num3
from 表1 a
full join 表2 b on a.YID = b.YID and a.MID = b.MID
full join 表3 c on a.YID = c.YID and a.MID = c.MID
select a.YID,A.MID,A.num1,b.num2,c.num3
from 表1 a
full join 表2 b on a.YID = b.YID and a.MID = b.MID
full join 表3 c on a.YID = c.YID and a.MID = c.MID
select a.YID,a.MID,Num1,isnull(Num2,0),isnull(Num3,0) from 表1 a
left join 表2 b on a.YID=b.YID and a.MID=B.Mid
left join 表3 c on a.YID=c.YID and a.MID=c.Mid
IsNull(IsNull(A.MID,b.MID),c.MID),
IsNull(A.num1,0),IsNull(b.num2,0),IsNull(c.num3,0)
from 表1 a
full join 表2 b on a.YID = b.YID and a.MID = b.MID
full join 表3 c on a.YID = c.YID and a.MID = c.MID
,num1=isnull(a.num1,0)
,num2=isnull(a.num2,0)
,num3=isnull(b.num3,0)
from(
select yid=isnull(a.yid,b.yid),mid=isnull(a.mid,b.mid),a.num1,b.num2
from 表1 a full join 表2 b on a.yid=b.yid and a.mid=b.mid
) a full join 表3 b on a.yid=b.yid and a.mid=b.mid
declare @表1 table(YID int,MID int,Num1 money)
insert into @表1
select 2003,12,38
union all select 2003,11,40
union all select 2003,10,37declare @表2 table(YID int,MID int,Num2 money)
insert into @表2
select 2003,12,5
union all select 2003,10,9declare @表3 table(YID int,MID int,Num3 money)
insert into @表3
select 2003,12,35
union all select 2003,11,30
union all select 2003,10,28--查询
select yid=isnull(a.yid,b.yid),mid=isnull(a.mid,b.mid)
,num1=isnull(a.num1,0)
,num2=isnull(a.num2,0)
,num3=isnull(b.num3,0)
from(
select yid=isnull(a.yid,b.yid),mid=isnull(a.mid,b.mid),a.num1,b.num2
from @表1 a full join @表2 b on a.yid=b.yid and a.mid=b.mid
) a full join @表3 b on a.yid=b.yid and a.mid=b.mid/*--测试结果
yid mid num1 num2 num3
----------- ----------- --------------------- -------------- ----------
2003 12 38.0000 5.0000 35.0000
2003 10 37.0000 9.0000 28.0000
2003 11 40.0000 .0000 30.0000(所影响的行数为 3 行)
--*/
select yid=isnull(a.yid,b.yid),mid=isnull(a.mid,b.mid)
,num1=isnull(a.num1,0)
,num2=isnull(a.num2,0)
,num3=isnull(b.num3,0)
from(
select yid=isnull(a.yid,b.yid),mid=isnull(a.mid,b.mid),a.num1,b.num2
from (表1查询语句) a full join (表2查询语句) b on a.yid=b.yid and a.mid=b.mid
) a full join (表3查询语句) b on a.yid=b.yid and a.mid=b.mid