我写的一个sql语句
SELECT sfb_zddm ,tsxx_sm,tsxx_dj,kcb_dbbz*kcb_xbbz jcs,ddml_mzddm,kcb_mzddm,kcb_dbbz,kcb_xbbz,kcb_tsbm,kcb_kcs+kcb_fhs-kcb_fss kfss,sum(sfb_zds) zds,sum(sfb_fss) zfss FROM ddml,kcb,sfb,zdpc,qxhtsbm,tsxxzd WHERE ddml_mlbh = zdpc_bh AND sfb_zddm = ddml_zddm AND tsxx_tsbm=kcb_tsbm AND sfb_jcd = '370130' AND kcb_jcd= '370130' AND ddml_sjdm = qxhtsbm_zdqh||qxhtsbm_zdxh AND kcb_tsbm = qxhtsbm_tsbm AND (kcb_kcs+kcb_fhs-kcb_fss) >0 GROUP BY sfb_zddm,tsxx_sm,kcb_kcs,kcb_fhs,kcb_fss,tsxx_dj,ddml_mzddm,kcb_mzddm,kcb_dbbz,kcb_xbbz,kcb_tsbm HAVING SUM(sfb_zds) - SUM(sfb_fss) > 0 ORDER BY zds desc,kcb_tsbm,kfss desc,sfb_zddm
其中zdpc_bh、sfb_zddm、ddml_zddm、tsxx_tsbm、kcb_tsbm、qxhtsbm_zdqh、qxhtsbm_zdxh 、qxhtsbm_tsbm 都是主键,可是查询半天出不来结果,如果把qxhtsbm这个表去掉,将ddml_sjdm = qxhtsbm_zdqh||qxhtsbm_zdxh AND kcb_tsbm = qxhtsbm_tsbm 这个关联改成ddml_sjdm = kcb_tsbm马上就出来了 ,qxhtsbm这个表就三个字段qxhtsbm_zdqh、qxhtsbm_zdxh 、qxhtsbm_tsbm 全是主键,而且这个表的数据不是很多,差不多是这几个表数据最少的,可是一加上这个关联就出不来了,以前的时候没加这个表,后来专门加了这个表是代码和编码的一个对照,以前代码和编码的对照没单独拿出来,直接可以通过ddml_sjdm = kcb_tsbm关联,现在把代码和编码的对照单独提出来了放在qxhtsbm这个表,代码和编码的对照是多对多关系,加上之后死活出不来结果。大家给想个办法,到底哪个地方出了问题。
SELECT sfb_zddm ,tsxx_sm,tsxx_dj,kcb_dbbz*kcb_xbbz jcs,ddml_mzddm,kcb_mzddm,kcb_dbbz,kcb_xbbz,kcb_tsbm,kcb_kcs+kcb_fhs-kcb_fss kfss,sum(sfb_zds) zds,sum(sfb_fss) zfss FROM ddml,kcb,sfb,zdpc,qxhtsbm,tsxxzd WHERE ddml_mlbh = zdpc_bh AND sfb_zddm = ddml_zddm AND tsxx_tsbm=kcb_tsbm AND sfb_jcd = '370130' AND kcb_jcd= '370130' AND ddml_sjdm = qxhtsbm_zdqh||qxhtsbm_zdxh AND kcb_tsbm = qxhtsbm_tsbm AND (kcb_kcs+kcb_fhs-kcb_fss) >0 GROUP BY sfb_zddm,tsxx_sm,kcb_kcs,kcb_fhs,kcb_fss,tsxx_dj,ddml_mzddm,kcb_mzddm,kcb_dbbz,kcb_xbbz,kcb_tsbm HAVING SUM(sfb_zds) - SUM(sfb_fss) > 0 ORDER BY zds desc,kcb_tsbm,kfss desc,sfb_zddm
其中zdpc_bh、sfb_zddm、ddml_zddm、tsxx_tsbm、kcb_tsbm、qxhtsbm_zdqh、qxhtsbm_zdxh 、qxhtsbm_tsbm 都是主键,可是查询半天出不来结果,如果把qxhtsbm这个表去掉,将ddml_sjdm = qxhtsbm_zdqh||qxhtsbm_zdxh AND kcb_tsbm = qxhtsbm_tsbm 这个关联改成ddml_sjdm = kcb_tsbm马上就出来了 ,qxhtsbm这个表就三个字段qxhtsbm_zdqh、qxhtsbm_zdxh 、qxhtsbm_tsbm 全是主键,而且这个表的数据不是很多,差不多是这几个表数据最少的,可是一加上这个关联就出不来了,以前的时候没加这个表,后来专门加了这个表是代码和编码的一个对照,以前代码和编码的对照没单独拿出来,直接可以通过ddml_sjdm = kcb_tsbm关联,现在把代码和编码的对照单独提出来了放在qxhtsbm这个表,代码和编码的对照是多对多关系,加上之后死活出不来结果。大家给想个办法,到底哪个地方出了问题。
按照我的规则改吧
SELECT sfb_zddm,
tsxx_sm,
tsxx_dj,
kcb_dbbz * kcb_xbbz jcs,
ddml_mzddm,
kcb_mzddm,
kcb_dbbz,
kcb_xbbz,
kcb_tsbm,
kcb_kcs + kcb_fhs - kcb_fss kfss,
sum(sfb_zds) zds,
sum(sfb_fss) zfss
FROM ddml, kcb, sfb, zdpc, qxhtsbm, tsxxzd
WHERE ddml_mlbh = zdpc_bh
AND sfb_zddm = ddml_zddm
AND tsxx_tsbm = kcb_tsbm
AND sfb_jcd = '370130'
AND kcb_jcd = '370130'
AND ddml_sjdm = qxhtsbm_zdqh || qxhtsbm_zdxh
AND kcb_tsbm = qxhtsbm_tsbm
AND (kcb_kcs + kcb_fhs - kcb_fss) > 0
GROUP BY sfb_zddm,
tsxx_sm,
kcb_kcs,
kcb_fhs,
kcb_fss,
tsxx_dj,
ddml_mzddm,
kcb_mzddm,
kcb_dbbz,
kcb_xbbz,
kcb_tsbm
HAVING SUM(sfb_zds) - SUM(sfb_fss) > 0
ORDER BY zds desc, kcb_tsbm, kfss desc, sfb_zddm;
--select 后面的---sum之前的字段,应和group by的字段保持一致!!!请楼主自已改一下。
如果你的表都分析过,应该会走CBO,这样,from和where后面的顺序无所谓.
主要的优化还是建索引,还慢的话再看看执行计划.