子查询返回的值不止一个。当子查询跟随在 =、=、<<=、>>= 之后,或子查询用作表达式时,这种情况是不允许的。该如何处理
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
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
------解决方案--------------------
(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)
也就是说你这些子查询的数据有可能返回的是多个!
------解决方案--------------------
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
------解决方案--------------------
(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)
也就是说你这些子查询的数据有可能返回的是多个!
------解决方案--------------------
- SQL code
SELECT * 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 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)
上面这些子查询返回的值不唯一,就会这样,改成下面试试
- SQL code
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
------解决方案--------------------