select * from (select *,(select xmwyh from xt_xmryk where rybh=a.bh) as xmwyh ,(select xm from xt_ryb where bh=a.bh) as xm ,(select lxmc from ry_zjlxb where zjlxid=a.zjlxid)as lxmc from ry_zjfjb a )as b
调试欢乐多
FROM ( SELECT * ,
( SELECT TOP(1) xmwyh --如果子查询结果集只返回一行,则没问题.否则失败.加个TOP(1),此SQL正确
FROM xt_xmryk
WHERE rybh = a.bh
) AS xmwyh ,
( SELECT TOP(1) xm --同上
FROM xt_ryb
WHERE bh = a.bh
) AS xm ,
( SELECT TOP(1) lxmc --同上
FROM ry_zjlxb
WHERE zjlxid = a.zjlxid
) AS lxmc
FROM ry_zjfjb a
) AS b
(select xm from xt_ryb where bh=a.bh) as xm ,
(select lxmc from ry_zjlxb where zjlxid=a.zjlxid)
上面这些子查询返回的值不唯一,就会这样,改成下面试试select a.*,b.xmwyh,c.xm,d.lxmc
from ry_zjfjb a
left join xt_xmryk b on a.bh=b.ybh
left join xt_ryb c on a.bh=c.bh
left join ry_zjlxb d on a.zjlxid=d.zjlxid
select * from (select *,(select max(xmwyh) from xt_xmryk where rybh=a.bh) as xmwyh ,(select max(xm) from xt_ryb where bh=a.bh) as xm ,(select max(lxmc) from ry_zjlxb where zjlxid=a.zjlxid)as lxmc from ry_zjfjb a )as b
这个想一下就清楚的,只有通过在子查询中加入"TOP 1"来限定读取的记录数。