SQL查询就改变一个连接方式，速度竟快了10多倍
SELECT VXFTKeHuHuoPinYanSeGuiGeU.FH_KeHuBH,
CKeHu.KH_XingMing,
VXFTKeHuHuoPinYanSeGuiGeU.FHMX_HuoPinBH,
VXFTKeHuHuoPinYanSeGuiGeU.FHMX_YanSe,
VXFTKeHuHuoPinYanSeGuiGeU.FHMX_GuiGe,
ROUND(isnull(VXFSum.FShuLiang,0),0) AS FZongXiaoLiang ,
ROUND(isnull(VXFSum.FJinE,0),2) AS FZongJinE ,
ROUND(isnull(VXTSum.TShuLiang,0),0) AS XZongTuiLiang ,
ROUND(isnull(VXTSum.TJinE,0),2) AS XZongJinE ,
ROUND(isnull(VXFSum.FShuLiang,0) - isnull(VXTSum.TShuLiang,0),0) AS ShiJiXiaoLiang ,
ROUND(isnull(VXFSum.FJinE,0) - isnull(VXTSum.TJinE,0),2) AS ShiJiXiaoShouJinE ,
ROUND((isnull(VXFSum.FShuLiang,0) - isnull(VXTSum.TShuLiang,0))/(isnull(a.F_ZSL,0) - isnull(b.T_ZSL,0)),4) AS ShiJiXiaoLiangBiLi ,
ROUND((isnull(VXFSum.FJinE,0) - isnull(VXTSum.TJinE,0))/(isnull(a.F_ZJE,0) - isnull(b.T_ZJE,0)),4) AS ShiJiXiaoShouJinEBiLi
FROM ( SELECT DISTINCT EXiaoShouFaHuo.FH_KeHuBH,
EXiaoShouFaHuoMingXi.FHMX_HuoPinBH,
EXiaoShouFaHuoMingXi.FHMX_YanSe,
EXiaoShouFaHuoMingXi.FHMX_GuiGe
FROM EXiaoShouFaHuo INNER JOIN
EXiaoShouFaHuoMingXi ON
EXiaoShouFaHuo.FH_FaHuoDanHao = EXiaoShouFaHuoMingXi.FHMX_FaHuoDanHao
WHERE (EXiaoShouFaHuo.FH_Date > = ( '2000-1-1 ')) AND
(EXiaoShouFaHuo.FH_Date <= ( '2008-1-1 '))
UNION
SELECT DISTINCT EXiaoShouTuiHuo.TH_KeHuBH,
EXiaoShouTuiHuoMingXi.THMX_HuoPinBH,
EXiaoShouTuiHuoMingXi.THMX_YanSe,
EXiaoShouTuiHuoMingXi.THMX_GuiGe
FROM EXiaoShouTuiHuo INNER JOIN
EXiaoShouTuiHuoMingXi ON
EXiaoShouTuiHuo.TH_TuiHuoDanHao = EXiaoShouTuiHuoMingXi.THMX_TuiHuoDanHao
WHERE (EXiaoShouTuiHuo.TH_Date > = ( '2000-1-1 ')) AND
(EXiaoShouTuiHuo.TH_Date <= ( '2008-1-1 ')) ) VXFTKeHuHuoPinYanSeGuiGeU
INNER JOIN --////我就把这里改成 LEFT OUTER JOIN
CKeHu
ON
VXFTKeHuHuoPinYanSeGuiGeU.FH_KeHuBH = CKeHu.KH_BianHao
LEFT OUTER JOIN
( SELECT EXiaoShouTuiHuo.TH_KeHuBH,
EXiaoShouTuiHuoMingXi.THMX_HuoPinBH,
EXiaoShouTuiHuoMingXi.THMX_YanSe,
EXiaoShouTuiHuoMingXi.THMX_GuiGe,
SUM(EXiaoShouTuiHuoMingXi.THMX_ShuLiang) AS TShuLiang,
SUM(EXiaoShouTuiHuoMingXi.THMX_ShuLiang * EXiaoShouTuiHuoMingXi.THMX_DanJia
* EXiaoShouTuiHuoMingXi.THMX_ZheKou) AS TJinE
FROM EXiaoShouTuiHuo INNER JOIN
EXiaoShouTuiHuoMingXi ON
EXiaoShouTuiHuo.TH_TuiHuoDanHao = EXiaoShouTuiHuoMingXi.THMX_TuiHuoDanHao
WHERE (EXiaoShouTuiHuo.TH_Date > = ( '2000-1-1 ')) AND
(EXiaoShouTuiHuo.TH_Date <= ( '2008-1-1 '))
GROUP BY EXiaoShouTuiHuo.TH_KeHuBH,
EXiaoShouTuiHuoMingXi.THMX_HuoPinBH,
EXiaoShouTuiHuoMingXi.THMX_YanSe,
EXiaoShouTuiHuoMingXi.THMX_GuiGe ) VXTSum
ON VXFTKeHuHuoPinYanSeGuiGeU.FHMX_HuoPinBH = VXTSum.THMX_HuoPinBH AND
VXFTKeHuHuoPinYanSeGuiGeU.FHMX_YanSe = VXTSum.THMX_YanSe AND
VXFTKeHuHuoPinYanSeGuiGeU.FHMX_GuiGe = VXTSum.THMX_GuiGe AND
VXFTKeHuHuoPinYanSeGuiGeU.FH_KeHuBH = VXTSum.TH_KeHuBH
LEFT OUTER JOIN
( SELECT EXiaoShouFaHuo.FH_KeHuBH,
EXiaoShouFaHuoMingXi.FHMX_HuoPinBH,
EXiaoShouFaHuoMingXi.FHMX_YanSe,
EXiaoShouFaHuoMingXi.FHMX_GuiGe,
SUM(EXiaoShouFaHuoMingXi.FHMX_ShuLiang) AS FShuLiang,
SUM(EXiaoShouFaHuoMingXi.FHMX_ShuLiang * EXiaoShouFaHuoMingXi.FHMX_DanJia
* EXiaoShouFaHuoMingXi.FHMX_ZheKou) AS FJinE
FROM EXiaoShouFaHuo INNER JOIN
EXiaoShouFaHuoMingXi ON
EXiaoShouFaHuo.FH_FaHuoDanHao = EXiaoShouFaHuoMingXi.FHMX_FaHuoDanHao
WHERE (EXiaoShouFaHuo.FH_Date > = ( '2000-1-1 ')) AND
(EXiaoShouFaHuo.FH_Date <= ( '2008-1-1 '))
GROUP BY EXiaoShouFaHuo.FH_KeHuBH,
EXiaoShouFaHuoMingXi.FHMX_HuoPinBH,
EXiaoShouFaHuoMingXi.FHMX_YanSe,
EXiaoShouFaHuoMingXi.FHMX_GuiGe ) VXFSum
ON VXFTKeHuHuoPinYanSeGuiGeU.FHMX_HuoPinBH = VXFSum.FHMX_HuoPinBH AND
VXFTKeHuHuoPinYanSeGuiGeU.FHMX_YanSe = VXFSum.FHMX_YanSe AND
VXFTKeHuHuoPinYanSeGuiGeU.FHMX_GuiGe = VXFSum.FHMX_GuiGe AND
VXFTKeHuHuoPinYanSeGuiGeU.FH_KeHuBH = VXFSum.FH_KeHuBH ,
(SELECT SUM(EXiaoShouFaHuoMingXi.FHMX_ShuLiang) AS F_ZSL,
SUM(EXiaoShouFaHuoMingXi.FHMX_ShuLiang * EXiaoShouFaHuoMingXi.FHMX_DanJia
* EXiaoShouFaHuoMingXi.FHMX_ZheKou) AS F_ZJE
FROM EXiaoShouFaHuo INNER JOIN
EXiaoShouFaHuoMingXi ON
EXiaoShouFaHuo.FH_FaHuoDanHao = EXiaoShouFaHuoMingXi.FHMX_FaHuoDanHao
WHERE (EXiaoShouFaHuo.FH_Date > = ( '2000-1-1 ') AND
EXiaoShouFaHuo.FH_Date <= ( '2008-1-1 '))) a ,
(SELECT SUM(EXiaoShouTuiHuoMingXi.THMX_ShuLiang) AS T_ZSL,
SUM(EXiaoShouTuiHuoMingXi.THMX_ShuLiang * EXiaoShouTuiHuoMingXi.THMX_DanJia
* EXiaoShouTuiHuoMingXi.THMX_ZheKou) AS T_ZJE
FROM EXiaoShouTuiHuoMingXi INNER JOIN
EXiaoShouTuiHuo ON
EXiaoShouTuiHuoMingXi.THMX_TuiHuoDanHao = EXiaoShouTuiHuo.TH_TuiHuoDanHao
WHERE (EXiaoShouTuiHuo.TH_Date > = ( '2000-1-1 ') AND
EXiaoShouTuiHuo.TH_Date <= ( '2008-1-1 '))) b
ORDER BY VXFTKeHuHuoPinYanSeGuiGeU.FH_KeHuBH,
ShiJiXiaoLiang DESC ;
----------------------------------
上面的查询语句，我就把这句
INNER JOIN
CKeHu
改成
LEFT OUTER JOIN
CKeHu
速度竟快了10倍（原来要200多秒，改后只要15秒左右），让我感到非常奇怪，大家觉得这样的查询还可以再优化吗？
[解决办法]
INNER JOIN 和OUTER JOIN的含义大相径庭,这种 "优化 "比较危险,楼主应该慎重.
[解决办法]
INNER JOIN和LEFT OUTER JOIN的结果不同，lz请注意
[解决办法]
SQL语句的优化就是平时得出来的经验~
[解决办法]
是两种不相同的连接方式
楼主最好看一下使用的方法
left join ,inner join ,full join
[解决办法]
INNER JOIN
CKeHu
改成
LEFT OUTER JOIN
CKeHu
------------------?这两种联系的意思是不一样的，要根据你的需要来取。不能光求速度而忽视业务要求。
