求助:如何实现三个表的全连接查询?有三个表,分别表示三个书架的书目列表:
第一个表:table1
Aname
----
H1
H2
H3 第二个表:table2
Bname
----
H1
H2
H5
H6第三个表:table3
Cname
----
H1
H5
H6
H9我用一下全链接查看table1和table2的数目汇总对比
select * from table1 full join Bname on table1.Aname=table2.Bname
Aname Bname
-------------
H1 H1
H2 H2
H3
H5
H6
现在想出一张汇总单子,对三个书架的书进行对比,输出如下结果,应该如何写SQL?Aname Bname Cname
-----------------------
H1 H1 H1
H2 H2
H3
H5 H5
H6
H9
(oracle10G)
第一个表:table1
Aname
----
H1
H2
H3 第二个表:table2
Bname
----
H1
H2
H5
H6第三个表:table3
Cname
----
H1
H5
H6
H9我用一下全链接查看table1和table2的数目汇总对比
select * from table1 full join Bname on table1.Aname=table2.Bname
Aname Bname
-------------
H1 H1
H2 H2
H3
H5
H6
现在想出一张汇总单子,对三个书架的书进行对比,输出如下结果,应该如何写SQL?Aname Bname Cname
-----------------------
H1 H1 H1
H2 H2
H3
H5 H5
H6
H9
(oracle10G)
你是不是想要下面这样的结果呀?aname bname cname
h1 h1 h1
h2 h2
h3
h5 h5
h6 h6
h9
(select table1.aname,table2.bname,
case when table1.aname is null then table2.bname else table1.aname end x
from table1 full join table2 on table1.aname=table2.bname) a full join table3 on a.x=table3.cname
order by a.aname,a.bname
Aname Bname Cname
-----------------------
H1 H1 H1
H2 H2
H3
H5 H5
H6 H6
H9
Aname Bname Cname
-----------------------
H1______H1______H1
H2______H2______
H3
________H5______H5
________H6______H6
________________H9
create table table1(Aname varchar(10))
insert into table1 values('H1')
insert into table1 values('H2')
insert into table1 values('H3')
create table table2(Bname varchar(10))
insert into table2 values('H1')
insert into table2 values('H2')
insert into table2 values('H5')
insert into table2 values('H6')
create table table3(Cname varchar(10))
insert into table3 values('H1')
insert into table3 values('H5')
insert into table3 values('H6')
insert into table3 values('H9')
goselect isnull(t1.Aname,'') Aname,
isnull(t2.Bname,'') Bname,
isnull(t3.Cname,'') Cname
from (select aname name from table1 union select bname name from table2 union select cname name from table3) m
left join table1 t1 on m.name = t1.aname
left join table2 t2 on m.name = t2.bname
left join table3 t3 on m.name = t3.cnamedrop table table1 , table2 , table3/*
Aname Bname Cname
---------- ---------- ----------
H1 H1 H1
H2 H2
H3
H5 H5
H6 H6
H9(所影响的行数为 6 行)*/