56,687
社区成员
发帖
与我相关
我的任务
分享
表A:
ID NAME PARENTKEY
-------------------------------
1 A NULL
2 B NULL
3 C 1
表B:
NAME HEIGHT WEIGHT
---------------------------------
A 10 10
B 20 20
C 30 30
NAME HEIGHT WEIGHT
---------------------------------
A 10 10
B 20 20
A 30 30
select case when a2.id is null then a1.name else a2.name end,weight,height
from a a1
left join a a2
on a1.parentkey = a2.id
inner join b
on a1.name = b.name
[/quote]
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
学习了。。。真心感谢上面所有的朋友!
继续学习ing……
我想要的结果应该是全部列出b表信息,并在a表中找出对应的,且按照id,parentkey关系列出来,这么看是不是应该用right join更准确?不过也有可能异常删除或修改导致b表里有而a里没有的话如果查出a为null的话好像也不太对,所以保险起见就用inner?
select case when a2.id is null then a1.name else a2.name end,weight,height
from a a1
left join a a2
on a1.parentkey = a2.id
inner join b
on a1.name = b.name
select case when a2.id is null then a1.name else a2.name end,weight,height
from a a1
left join a a2
on a1.parentkey = a2.id
left join b
on a1.name = b.name
select case when a1.parentkey is null then a1.name else a2.name end, b.height,b.weight
from a a1,a a2,b
where a1.parentkey=a2.id and a1.name=b.name
我想要的是
name height weight
----------------------------------
a 10 10
b 20 20
a 30 30