select a.khmc as 客户名称,b.mc as khzt,c.mc as khxz,d.mc as city
from 表B a,表A b,表A c,表A d
where (a.khzt=b.bh and b.lx='khzt) and (a.khxz=c.bh and c.lx='khxz')
and (a.city=d.bh and d.lx='city')
from 表B a,表A b,表A c,表A d
where (a.khzt=b.bh and b.lx='khzt) and (a.khxz=c.bh and c.lx='khxz')
and (a.city=d.bh and d.lx='city')
from 表B a, 表A b, 表A c, 表A d
where b.lx='khzt' and b.bh = a.khzt
and c.lx='khxz' and c.bh = a.khxz
and d.lx='city' and d.bh = a.city
在我提出的建议中没有考虑到SQL的优化问题,如果表A是字典表则应该为:
select a.khmc as 客户名称,b.mc as khzt,c.mc as khxz,d.mc as city
from 表A b,表A c,表A d,表B a
where (a.khzt=b.bh and b.lx='khzt) and (a.khxz=c.bh and c.lx='khxz')
and (a.city=d.bh and d.lx='city')
根据是:当多表关联时,尽量将数据量小的表放在靠近from的地方