select a.cid,case when b.aname is null then c.bname else b.aname end as cname,a.crelation
from C表 a
left join A表 b on a.cid=b.aid
left join B表 c on a.cid=c.bid
from C表 a
left join A表 b on a.cid=b.aid
left join B表 c on a.cid=c.bid
select Cid,aName as CName , CRelation
from tb,tb3
where tb.aid=tb3.cid union select Cid,bName as CName , CRelation
from tb2,tb3
where tb2.bid=tb3.cid 结果: a1 one 0
a2 two 0
b1 Bone 0
b2 Btwo 0
union
select b.bid, b.bname,c.crelation from b, c where b.bid = c.cid
from tb,tb3
where tb.aid=tb3.cid union select Cid,bName as CName , CRelation
from tb2,tb3
where tb2.bid=tb3.cid
--学习一楼
declare @a table(aid varchar(20),aname varchar(20))
insert into @a
select 'a1','one' union all
select 'a2','two' declare @b table(bid varchar(20),bname varchar(20))
insert into @b
select 'b1','bone' union all
select 'b2','btwo'
declare @c table(cid varchar(20),crelation int)
insert into @c
select 'a1',0 union all
select 'a2',0 union all
select 'b1',0 union all
select 'b2',0
select a.cid,case when b.aname is null then c.bname else b.aname end as cname,a.crelation
from @c a
left join @a b on a.cid=b.aid
left join @b c on a.cid=c.bid