select a.name, b.fbno, b.msg from A表 left join B表 on a.no=b.no
上面理解有问题了怎么知道一个人的父子关系,是通过msg字段关联么?
select t.name 姓名,父亲姓名=(select name from A where t.fbno=no),B.msg from A t,B where t.no=B.no
select a.name,aa.name as fatherName,b.msg,aa.msg as fatherMsg from A a inner join B b on a.no=b.no inner join A aa on b.fbno=aa.no
首先,name和no都是数据库里面的关键字,所以不能用,我下面改为了nname和nnno 思路:把A表和B表使用Left Join关联起来,形成一张有(姓名、编号、父编号、信息)四个字段的虚拟表,然后通过Inner join来查询这张虚拟表就可以了。SELECT 姓名=c.nname,父亲姓名=d.nname,信息=c.msg FROM(SELECT a.nname,a.nno,b.fbno,b.msg FROM A表 AS a LEFT JOIN B表 AS b ON a.nno=b.nno ) AS c INNER JOIN (SELECT a.nname,a.nno,b.fbno,b.msg FROM A表 AS a LEFT JOIN B表 AS b ON a.nno=b.nno ) AS d ON c.fbno=d.nno AND c.nname='一个人的姓名'如果要查询所有人,那么去掉AND c.nname='一个人的姓名',希望对你你有帮助,我也是新手。
或者向楼上所说的那样也可以实现,具体的我没看太懂,望指教。SELECT 姓名=a.nname,父亲姓名=c.nname,信息=b.msg FROM A表 a INNER JOIN B表 b ON a.nno=b.nno INNER JOIN A表 c ON b.fbno=c.nno [code=SQL]AND a.nname='一个人的姓名' [/code] 同样的去除AND a.nname='一个人的姓名'这句话
select a.name,
b.fbno,
b.msg
from A表
left join B表
on a.no=b.no
where t.no=B.no
from A a inner join B b on a.no=b.no
inner join A aa on b.fbno=aa.no
思路:把A表和B表使用Left Join关联起来,形成一张有(姓名、编号、父编号、信息)四个字段的虚拟表,然后通过Inner join来查询这张虚拟表就可以了。SELECT 姓名=c.nname,父亲姓名=d.nname,信息=c.msg
FROM(SELECT a.nname,a.nno,b.fbno,b.msg
FROM A表 AS a
LEFT JOIN B表 AS b
ON a.nno=b.nno
) AS c
INNER JOIN (SELECT a.nname,a.nno,b.fbno,b.msg
FROM A表 AS a
LEFT JOIN B表 AS b
ON a.nno=b.nno
) AS d
ON c.fbno=d.nno AND c.nname='一个人的姓名'如果要查询所有人,那么去掉AND c.nname='一个人的姓名',希望对你你有帮助,我也是新手。
FROM A表 a INNER JOIN B表 b ON a.nno=b.nno
INNER JOIN A表 c ON b.fbno=c.nno [code=SQL]AND a.nname='一个人的姓名'
[/code]
同样的去除AND a.nname='一个人的姓名'这句话