表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 nullSQL
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 nullSQL
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
(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.idnameA nameB nameC
--------------- -------------------------- --------------------
a NULL NULL
a b NULL
a b2 NULL
a b c(4 行受影响)
from table1 a
left join table2 b on a.nameA=b.nameB
left join table3 c on a.nameA=c.nameC
union all
如果nameA的名字与nameB的名字一样就不可以了? 也就是说nameA与nameB还有nameC的名字不能重复。这样做局限性太大了。
还有就是,就在现在目前的数据条数查询来的结果是没问题,但是一条数据多了,就会有问题了。
如果说 现在可以查询出nameA nameB nameC
--------------- -------------------------- --------------------
a NULL NULL
a b NULL
a b2 NULL
a b c那如果数据是表1
A.id A.NameA
1 a
2 b
表2
B.id B.NameB B.Aid
1 b 1
2 b2 1
3 b4 2
表3
C.id C.NameC C.Bid
1 c 1
2 c1 2
3 c2 3如果是这样的数据查询出来的结果就不是我想要的啦。
我想要的结果是nameA nameB nameC
--------------- -------------------------- --------------------
a NULL NULL
a b NULL
a b c
a b2 NULL
a b2 c1
b NULL NULL
b b4 NULL
b b4 c2是这样的, 查询出来的结果就像是树形结构似的。
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
--------------- -------------------------- --------------------
a NULL NULL
b NULL NULL
a b1 NULL
a b2 NULL
b b4 NULL
a b1 c
a b2 c1
b b4 c2这样的结果是先查询大类排列出来在查询资子类排列出来。。没有像树形结构交叉。
select a.nameA,b.nameB,c.nameC
from table1 a
join table2 b on a.id=b.aid
join table3 c on b.id=c.bid
for xml auto