SELECT NO,XM,ZFY FROM
(
SELECT NO,XM,
NVL((SELECT SUM(ZFY) FROM menzhen B WHERE A.NO=B.NO),0)+
NVL((SELECT SUM(FYZJ) FROM zhuyuan B WHERE A.NO=B.NO),0) AS ZFY,NVL((SELECT SUM(jf_MZ) FROM menzhen B WHERE A.NO=B.NO),0)+
NVL((SELECT SUM(jf_ZY) FROM zhuyuan B WHERE A.NO=B.NO),0) AS JF
FROM person A
) X
WHERE X.JF>0我在执行这个语句时,只执行里面的查询速度很快,可一起执行时,没有结果,不知是很慢,还是出不来,请各位大侠给指教.
(
SELECT NO,XM,
NVL((SELECT SUM(ZFY) FROM menzhen B WHERE A.NO=B.NO),0)+
NVL((SELECT SUM(FYZJ) FROM zhuyuan B WHERE A.NO=B.NO),0) AS ZFY,NVL((SELECT SUM(jf_MZ) FROM menzhen B WHERE A.NO=B.NO),0)+
NVL((SELECT SUM(jf_ZY) FROM zhuyuan B WHERE A.NO=B.NO),0) AS JF
FROM person A
) X
WHERE X.JF>0我在执行这个语句时,只执行里面的查询速度很快,可一起执行时,没有结果,不知是很慢,还是出不来,请各位大侠给指教.
NVL((SELECT SUM(ZFY) FROM menzhen B WHERE A.NO=B.NO),0)+
NVL((SELECT SUM(FYZJ) FROM zhuyuan B WHERE A.NO=B.NO),0) AS ZFY,
NVL((SELECT SUM(jf_MZ) FROM menzhen B WHERE A.NO=B.NO),0)+
NVL((SELECT SUM(jf_ZY) FROM zhuyuan B WHERE A.NO=B.NO),0) AS JF
你最好合并一下速度就上去了
select a.no,a.xm,nvl(sum(b.zfy),0)+nvl(sum(b.fyzj),0) zfy
from a,b
where exists(
select nvl(sum(jf_mz),0)+nvl(sum(jf_zy),0)
from a,b
where a.no=b.no
)
group by a.no,a.xm
/
至于你说的查询速度慢要看表是否已经建了索引,注意主键外键不能为空!
你的语句也可以给写成如下形式:
SELECT A.NO,A.XM,V.ZFY FROM person A LEFT JOIN (
SELECT SUM(ZFY) AFY,SUM(jf_MZ) JF_MZ,NO FROM menzhen B GROUP BY B.NO)V ON (V.NO = A.NO)
LEFT JOIN (
SELECT SUM(FYZJ) FYZJ,SUM(jf_ZY) JF_ZY,NO FROM zhuyuan C GROUP BY C.NO)VV ON (VV.NO = A.NO) WHERE V.JF_MZ+VV.JF_ZY>O
是说:这条语句运行了好长时间不出结果.
另外jf_MZ和jf_ZY字段如果有空值,是不是对查询有影响,还是对查询结果有影响呀?