表1
A.id A.NameA
1 a
表2
B.id B.NameB B.Aid
1 b 1
2 b2 1
表3
C.id C.NameC C.Bid
1 c 1
表结构就是这样，我想查询出来的结构是：
NameA NameB NameC
a null null
a b null
a b c
a b2 null
SQL
------解决方案--------------------
create table table1
(id int,nameA varchar(50))
create table table2
(id int,nameB varchar(50),aid int)
create table table3
(id int,nameC varchar(50),bid int)
insert into table1
select 1,'a'
insert into table2
select 1,'b',1 union all
select 2,'b2',1
insert into table3
select 1,'c',1
select a.nameA,b.nameB,c.nameC
from table1 a
left join table2 b on a.nameA=b.nameB
left join table3 c on a.nameA=c.nameC
union all
select a.nameA,b.nameB,c.nameC
from table1 a
left join table2 b on a.id=b.aid
left join table3 c on a.nameA=c.nameC
union all
select a.nameA,c.nameB,c.nameC
from table1 a
left join
(
select a.nameB,b.nameC,a.id
from table2 a
left join table3 b on a.id=b.bid
)c on a.id=c.id
------解决方案--------------------
WITH K as(
select id,NameA,null as NameB,null NameC from table1
union all
select b.id,b.NameA,a.NameB,null from table2 a left join table1 b on a.aid=b.id
union all
select c.id,c.NameA,b.NameB,a.Namec from table3 as a left join table2 b on a.bid=b.id left join table1 c on b.aid=c.id
)
select * from K