Question

三张表关联查询，a,b,c三张表，a分别与b,c一对多的SQl语句怎么写？

a表 b表 c表
aid name bid aid Hobby cid aid Specialty
1 q 1 1 a 1 1 z
2 w 2 1 s 2 1 x
3 e 3 2 d 3 2 c
4 3 f

结果：

aid name bid Hobby cid Specialty
1 q 1 a 1 z
2 s 2 x
2 w 3 d 3 c
3 e 4 f

Answer 1

这很难吗？这么简单的东西，外关联不就得了
SELECT DISTINCT A.AID,A.NAME,B.BID,B.HOBBY,C.CID,C.Specialty
FROM A,B,C
WEHRE A.AID=B.AID(+)
AND A.AID=C.AID(+)

追问

WEHRE A.AID=B.AID(+)
AND A.AID=C.AID(+)
(+)这是干嘛的？

追答

外关联呀

追问

谢谢，还是不对，看来SQL语句不好整啊，我去找其他方法...

追答

不对？我怕不会哦

Answer 2

select A.aid,A.name,A.bid,A.Hobby,c.cid,c.Specialty from (select a.aid,a.name,b.id,b.Hobby from a left join b on a.aid=b.aid)A left join c on A.aid=c.aid

Answer 3

select a.aid,a.name,b.bid,b.hobby,c.cid,c.Specialty
from b left join a on b.aid=a.aid left join c on b.aid=c.aid

追问

这么写达不到我要多效果啊！！！
谢谢

追答

哪里达不到？可以改！

追问

a表 b表 c表
aid name bid aid Hobby cid aid Specialty
1 q 1 1 a 1 1 z
2 w 2 1 s 2 1 x
3 e 3 2 d 3 2 c
4 3 f

结果：

aid name bid Hobby cid Specialty
1 q 1 a 1 z
2 s 2 x
2 w 3 d 3 c
3 e 4 f

能出来此效果再告诉我，谢谢

追答

select (case when num>1 then '' else aid end) aid,(case when num>1 then '' else name ) name,bid,Hobby,cid,Specialty
from
(select a.aid,a.name,b.bid,b.hobby,c.cid,c.Specialty,rank() over(partition by a.aid order by b.bid desc) num
from b left join a on b.aid=a.aid left join c on b.aid=c.aid ) t

Answer 4

何必那么麻烦，自己建个视图，把这3个表拖进去，然后选择需要的字段，系统会自动生成查询语句的。