表为生产情况,里面有明细,我要先求出一段时间内的某个物品的汇总数(汇总时有日期等条件),再求这个物品的历史汇总数(汇总时无条件),要将一段时间内的汇总数与该物品的历史汇总数显示在一起,两个汇总,分开来执行都在5秒以内,但连接显示在一起后,速度就慢的不能接受了(约2分钟)以下为参考:SELECT CONVERT(bit,0) AS xz,a.TC_SHState,a.TC_CSPsn,a.TC_FHPsn,a.TC_SHPsn,a.TC_CSDate,a.TC_FHDate,a.TC_SHDate,a.cancelshre,(CASE WHEN ISNULL(a.wwcs,'')<>'' THEN a.wwcs WHEN ISNULL(a.bb,'')<>'' THEN a.bb WHEN ISNULL(a.zb,'')<>'' THEN a.zb ELSE a.bm END) AS bmzb,a.ddid,a.lbid,a.zlid,b.ddh,b.hm,a.cpmc,CONVERT(DECIMAL(13,0),b.sl) AS ddsl,c.bjmc,d.gxmc,a.bs,c.cpyl,c.cpyl*b.sl AS sqsl,CONVERT(DECIMAL(13,2),0) AS yscsl,(CASE WHEN ISNULL(a.wwcs,'')='' AND ISNULL(a.wnstate,0)=0 THEN a.scsl ELSE 0 END) AS scsl,(CASE WHEN ISNULL(a.wnstate,0)=1 THEN a.scsl ELSE 0 END) AS wnsl,(CASE WHEN ISNULL(a.wwcs,'')<>'' THEN a.scsl ELSE 0 END) AS wwsl,a.dj,a.je,a.gxid,a.gxdtlid,a.cpxh,a.lb,a.dept,a.bjgzid,a.gxgzid,a.wwcs FROM (SELECT MAX(a.TC_SHState) AS TC_SHState,MAX(a.TC_CSPsn) AS TC_CSPsn,MAX(a.TC_FHPsn) AS TC_FHPsn,MAX(a.TC_SHPsn) AS TC_SHPsn,MAX(a.TC_CSDate) AS TC_CSDate,MAX(a.TC_FHDate) AS TC_FHDate,MAX(a.TC_SHDate) AS TC_SHDate,MAX(a.cancelshre) AS cancelshre,a.ddid,a.lbid,a.zlid,a.cpxh,a.lb,a.bs,a.cpmc,a.dept,a.bjgzid,a.gxgzid,a.bm,a.zb,a.bb,a.wwcs,a.wnstate,a.gxid,a.gxdtlid,SUM(a.scsl) AS scsl,MAX(a.dj) AS dj,SUM((CASE WHEN ISNULL(c.gjdw,0)<>0 THEN CONVERT(DECIMAL(13,2),a.scsl*a.dj/c.gjdw) ELSE 0 END)) AS je FROM TC_DDSCPerson_Day a LEFT OUTER JOIN dd_cpzl b ON a.ddid=b.ddid AND a.lbid=b.lbid AND a.zlid=b.zlid LEFT OUTER JOIN [#32e5ea55-90cb-438a-96c3-ca9afa3337ce] c ON a.cpxh=c.cpxh AND a.lb=c.lb AND a.bs=c.bs AND a.cpmc=c.cpmc AND a.dept=c.dept AND a.bjgzid=c.gzid WHERE 1=1 AND a.scrq>='2010-05-28' AND a.scrq<='2010-05-28' AND a.zlid<>-1 GROUP BY a.ddid,a.lbid,a.zlid,a.bm,a.zb,a.bb,a.wnstate,a.wwcs,a.gxid,a.gxdtlid,a.cpxh,a.lb,a.bs,a.cpmc,a.dept,a.bjgzid,a.gxgzid) a LEFT OUTER JOIN dd_cpzl b ON a.ddid=b.ddid AND a.lbid=b.lbid AND a.zlid=b.zlid LEFT OUTER JOIN [#32e5ea55-90cb-438a-96c3-ca9afa3337ce] c ON a.cpxh=c.cpxh AND a.lb=c.lb AND a.bs=c.bs AND a.cpmc=c.cpmc AND a.dept=c.dept AND a.bjgzid=c.gzid LEFT OUTER JOIN [#38bddcda-da7f-4185-bfbc-685830df89ef] d ON a.cpxh=d.cpxh AND a.lb=d.lb AND a.bs=d.bs AND a.cpmc=d.cpmc AND a.dept=d.dept AND a.bjgzid=d.bjgzid AND a.gxgzid=d.gzid ORDER BY a.wwcs DESC,a.bmzb,b.hm,b.cpmc;上面的语句是没有连接的SELECT CONVERT(bit,0) AS xz,a.TC_SHState,a.TC_CSPsn,a.TC_FHPsn,a.TC_SHPsn,a.TC_CSDate,a.TC_FHDate,a.TC_SHDate,a.cancelshre,(CASE WHEN ISNULL(a.wwcs,'')<>'' THEN a.wwcs WHEN ISNULL(a.bb,'')<>'' THEN a.bb WHEN ISNULL(a.zb,'')<>'' THEN a.zb ELSE a.bm END) AS bmzb,a.ddid,a.lbid,a.zlid,b.ddh,b.hm,a.cpmc,CONVERT(DECIMAL(13,0),b.sl) AS ddsl,c.bjmc,d.gxmc,a.bs,c.cpyl,c.cpyl*b.sl AS sqsl,CONVERT(DECIMAL(13,2),f.yscsl) AS yscsl,(CASE WHEN ISNULL(a.wwcs,'')='' AND ISNULL(a.wnstate,0)=0 THEN a.scsl ELSE 0 END) AS scsl,(CASE WHEN ISNULL(a.wnstate,0)=1 THEN a.scsl ELSE 0 END) AS wnsl,(CASE WHEN ISNULL(a.wwcs,'')<>'' THEN a.scsl ELSE 0 END) AS wwsl,a.dj,a.je,a.gxid,a.gxdtlid,a.cpxh,a.lb,a.dept,a.bjgzid,a.gxgzid,a.wwcs FROM (SELECT MAX(a.TC_SHState) AS TC_SHState,MAX(a.TC_CSPsn) AS TC_CSPsn,MAX(a.TC_FHPsn) AS TC_FHPsn,MAX(a.TC_SHPsn) AS TC_SHPsn,MAX(a.TC_CSDate) AS TC_CSDate,MAX(a.TC_FHDate) AS TC_FHDate,MAX(a.TC_SHDate) AS TC_SHDate,MAX(a.cancelshre) AS cancelshre,a.ddid,a.lbid,a.zlid,a.cpxh,a.lb,a.bs,a.cpmc,a.dept,a.bjgzid,a.gxgzid,a.bm,a.zb,a.bb,a.wwcs,a.wnstate,a.gxid,a.gxdtlid,SUM(a.scsl) AS scsl,MAX(a.dj) AS dj,SUM((CASE WHEN ISNULL(c.gjdw,0)<>0 THEN CONVERT(DECIMAL(13,2),a.scsl*a.dj/c.gjdw) ELSE 0 END)) AS je FROM TC_DDSCPerson_Day a LEFT OUTER JOIN dd_cpzl b ON a.ddid=b.ddid AND a.lbid=b.lbid AND a.zlid=b.zlid LEFT OUTER JOIN [#32e5ea55-90cb-438a-96c3-ca9afa3337ce] c ON a.cpxh=c.cpxh AND a.lb=c.lb AND a.bs=c.bs AND a.cpmc=c.cpmc AND a.dept=c.dept AND a.bjgzid=c.gzid WHERE 1=1 AND a.scrq>='2010-05-28' AND a.scrq<='2010-05-28' AND a.zlid<>-1 GROUP BY a.ddid,a.lbid,a.zlid,a.bm,a.zb,a.bb,a.wnstate,a.wwcs,a.gxid,a.gxdtlid,a.cpxh,a.lb,a.bs,a.cpmc,a.dept,a.bjgzid,a.gxgzid) a LEFT OUTER JOIN dd_cpzl b ON a.ddid=b.ddid AND a.lbid=b.lbid AND a.zlid=b.zlid LEFT OUTER JOIN [#32e5ea55-90cb-438a-96c3-ca9afa3337ce] c ON a.cpxh=c.cpxh AND a.lb=c.lb AND a.bs=c.bs AND a.cpmc=c.cpmc AND a.dept=c.dept AND a.bjgzid=c.gzid LEFT OUTER JOIN [#38bddcda-da7f-4185-bfbc-685830df89ef] d ON a.cpxh=d.cpxh AND a.lb=d.lb AND a.bs=d.bs AND a.cpmc=d.cpmc AND a.dept=d.dept AND a.bjgzid=d.bjgzid AND a.gxgzid=d.gzid
--从此处开始连接
LEFT OUTER JOIN f_SumYscsl_Bj() e ON a.ddid=e.ddid AND a.lbid=e.lbid AND a.zlid=e.zlid AND a.cpxh=e.cpxh AND a.lb=e.lb AND a.bs=e.bs AND a.bjgzid=e.bjgzid AND a.dept=e.dept LEFT OUTER JOIN f_SumYscsl_gx() f ON a.ddid=f.ddid AND a.lbid=f.lbid AND a.zlid=f.zlid AND a.cpxh=f.cpxh AND a.lb=f.lb AND a.bs=f.bs AND a.bjgzid=f.bjgzid AND a.gxgzid=f.gxgzid AND a.dept=f.dept
ORDER BY a.wwcs DESC,a.bmzb,b.hm,b.cpmc;
--从此处开始连接
LEFT OUTER JOIN f_SumYscsl_Bj() e ON a.ddid=e.ddid AND a.lbid=e.lbid AND a.zlid=e.zlid AND a.cpxh=e.cpxh AND a.lb=e.lb AND a.bs=e.bs AND a.bjgzid=e.bjgzid AND a.dept=e.dept LEFT OUTER JOIN f_SumYscsl_gx() f ON a.ddid=f.ddid AND a.lbid=f.lbid AND a.zlid=f.zlid AND a.cpxh=f.cpxh AND a.lb=f.lb AND a.bs=f.bs AND a.bjgzid=f.bjgzid AND a.gxgzid=f.gxgzid AND a.dept=f.dept
ORDER BY a.wwcs DESC,a.bmzb,b.hm,b.cpmc;
a.TC_SHDate, a.cancelshre, (
CASE
WHEN ISNULL(a.wwcs, '')<>'' THEN a.wwcs
WHEN ISNULL(a.bb, '')<>'' THEN a.bb
WHEN ISNULL(a.zb, '')<>'' THEN a.zb
ELSE a.bm
END
) AS bmzb, a.ddid, a.lbid, a.zlid, b.ddh, b.hm, a.cpmc, CONVERT(DECIMAL(13, 0), b.sl) AS ddsl,
c.bjmc, d.gxmc, a.bs, c.cpyl, c.cpyl*b.sl AS sqsl, CONVERT(DECIMAL(13, 2), f.yscsl) AS yscsl, (
CASE
WHEN ISNULL(a.wwcs, '')=''
AND ISNULL(a.wnstate, 0)=0 THEN a.scsl ELSE 0 END
) AS scsl, (CASE WHEN ISNULL(a.wnstate, 0)=1 THEN a.scsl ELSE 0 END) AS wnsl, (CASE WHEN ISNULL(a.wwcs, '')<>'' THEN a.scsl ELSE 0 END) AS
wwsl, a.dj, a.je, a.gxid, a.gxdtlid, a.cpxh, a.lb, a.dept, a.bjgzid, a.gxgzid, a.wwcs
FROM (
SELECT MAX(a.TC_SHState) AS TC_SHState, MAX(a.TC_CSPsn) AS TC_CSPsn, MAX(a.TC_FHPsn) AS
TC_FHPsn, MAX(a.TC_SHPsn) AS TC_SHPsn, MAX(a.TC_CSDate) AS TC_CSDate, MAX(a.TC_FHDate) AS
TC_FHDate, MAX(a.TC_SHDate) AS TC_SHDate, MAX(a.cancelshre) AS cancelshre, a.ddid,
a.lbid, a.zlid, a.cpxh, a.lb, a.bs, a.cpmc, a.dept, a.bjgzid, a.gxgzid, a.bm, a.zb,
a.bb, a.wwcs, a.wnstate, a.gxid, a.gxdtlid, SUM(a.scsl) AS scsl, MAX(a.dj) AS dj,
SUM(
(
CASE
WHEN ISNULL(c.gjdw, 0)<>0 THEN CONVERT(DECIMAL(13, 2), a.scsl*a.dj/c.gjdw)
ELSE 0
END
)
) AS je
FROM TC_DDSCPerson_Day a
LEFT OUTER JOIN dd_cpzl b
ON a.ddid = b.ddid
AND a.lbid = b.lbid
AND a.zlid = b.zlid
LEFT OUTER JOIN [#32e5ea55-90cb-438a-96c3-ca9afa3337ce] c
ON a.cpxh = c.cpxh
AND a.lb = c.lb
AND a.bs = c.bs
AND a.cpmc = c.cpmc
AND a.dept = c.dept
AND a.bjgzid = c.gzid
WHERE 1 = 1
AND a.scrq>= '2010-05-28'
AND a.scrq<= '2010-05-28'
AND a.zlid<>-1
GROUP BY a.ddid, a.lbid, a.zlid, a.bm, a.zb, a.bb, a.wnstate, a.wwcs, a.gxid, a.gxdtlid, a.cpxh,
a.lb, a.bs, a.cpmc, a.dept, a.bjgzid, a.gxgzid
) a
LEFT OUTER JOIN dd_cpzl b
ON a.ddid = b.ddid
AND a.lbid = b.lbid
AND a.zlid = b.zlid
LEFT OUTER JOIN [#32e5ea55-90cb-438a-96c3-ca9afa3337ce] c
ON a.cpxh = c.cpxh
AND a.lb = c.lb
AND a.bs = c.bs
AND a.cpmc = c.cpmc
AND a.dept = c.dept
AND a.bjgzid = c.gzid
LEFT OUTER JOIN [#38bddcda-da7f-4185-bfbc-685830df89ef] d
ON a.cpxh = d.cpxh
AND a.lb = d.lb
AND a.bs = d.bs
AND a.cpmc = d.cpmc
AND a.dept = d.dept
AND a.bjgzid = d.bjgzid
AND a.gxgzid = d.gzid
--从此处开始连接
LEFT OUTER JOIN f_SumYscsl_Bj() e
ON a.ddid = e.ddid
AND a.lbid = e.lbid
AND a.zlid = e.zlid
AND a.cpxh = e.cpxh
AND a.lb = e.lb
AND a.bs = e.bs
AND a.bjgzid = e.bjgzid
AND a.dept = e.dept
LEFT OUTER JOIN f_SumYscsl_gx() f
ON a.ddid = f.ddid
AND a.lbid = f.lbid
AND a.zlid = f.zlid
AND a.cpxh = f.cpxh
AND a.lb = f.lb
AND a.bs = f.bs
AND a.bjgzid = f.bjgzid
AND a.gxgzid = f.gxgzid
AND a.dept = f.dept
ORDER BY a.wwcs DESC, a.bmzb, b.hm, b.cpmc;看了一下,语句上似乎没什么可优化的,你在各表的连接字段和条件字段加上索引试试。