这条sql语句怎么优化,只有1056条记录,可是查询出来花了3分钟
select jfqkmc,zkdjr,zkdjsj,sfzsmc,rq,czr,xsdm,a.ksh,zkzh,xm,xbmc,mzmc,sfzjh,csrq,zzmmmc,dh,yzmc,yxmc,xnzymc,lqzymc,lqzy,byzxmc,byzxym,lqlbmc,tzsh,tjr,
case zsjj when 1 then '春季' else '秋季' end zsjj,xslymc,rxnf,zsss,jg,hkyszdz,rxzf,gkzf,fjf,fjflb,kstc,jlcc,tjjl,txdz,yzbm,CONVERT(VARCHAR(10),ZCRQ,120) AS ZCRQ,
(case when ZCRQ is not null then '是' else '否' end ) as ZCMC,CONVERT(VARCHAR(10),BDRQ,120) AS BDRQ,
(case when BDRQ is not null then '是' else '否' end ) as BDMC,CONVERT(VARCHAR(10),TXRQ,120) AS TXRQ,
(case when TXRQ is not null then '是' else '否' end ) as TXMC,
(case when isnull((select sum(b.JE) as zje from SF_SFDb c left join SF_SFDMX b on c.SFPJNM=b.SFPJNM
and c.XH=a.ksh),0)>0 then '已缴费' else '未缴费' end) as JFZT,BDCZR,ZCCZR,TXCZR
from V_ZS_SYXX a
left join (select jfqkmc,zkdjr,zkdjsj,ksh from V_ZS_ZKDJB) b on a.ksh = b.ksh
left join (select sfzsmc,rq,czr,ksh from V_ZS_ZSDJ) c on a.ksh = c.ksh
select jfqkmc,zkdjr,zkdjsj,sfzsmc,rq,czr,xsdm,a.ksh,zkzh,xm,xbmc,mzmc,sfzjh,csrq,zzmmmc,dh,yzmc,yxmc,xnzymc,lqzymc,lqzy,byzxmc,byzxym,lqlbmc,tzsh,tjr,
case zsjj when 1 then '春季' else '秋季' end zsjj,xslymc,rxnf,zsss,jg,hkyszdz,rxzf,gkzf,fjf,fjflb,kstc,jlcc,tjjl,txdz,yzbm,CONVERT(VARCHAR(10),ZCRQ,120) AS ZCRQ,
(case when ZCRQ is not null then '是' else '否' end ) as ZCMC,CONVERT(VARCHAR(10),BDRQ,120) AS BDRQ,
(case when BDRQ is not null then '是' else '否' end ) as BDMC,CONVERT(VARCHAR(10),TXRQ,120) AS TXRQ,
(case when TXRQ is not null then '是' else '否' end ) as TXMC,
(case when isnull((select sum(b.JE) as zje from SF_SFDb c left join SF_SFDMX b on c.SFPJNM=b.SFPJNM
and c.XH=a.ksh),0)>0 then '已缴费' else '未缴费' end) as JFZT,BDCZR,ZCCZR,TXCZR
from V_ZS_SYXX a
left join (select jfqkmc,zkdjr,zkdjsj,ksh from V_ZS_ZKDJB) b on a.ksh = b.ksh
left join (select sfzsmc,rq,czr,ksh from V_ZS_ZSDJ) c on a.ksh = c.ksh
估计问题在(select sum(b.JE) as zje from SF_SFDb c left join SF_SFDMX b on c.SFPJNM=b.SFPJNM
and c.XH=a.ksh),0)这
and c.XH=a.ksh这一句 是不是要改为 select sum(b.JE) as zje from SF_SFDb c left join SF_SFDMX b on c.SFPJNM=b.SFPJNM
where c.XH=a.ksh
改成按c.XH分组统计,然后join到V_ZS_SYXX表
try:select jfqkmc,zkdjr,zkdjsj,sfzsmc,rq,czr,xsdm,a.ksh,zkzh,xm,xbmc,mzmc,sfzjh,csrq,zzmmmc,dh,yzmc,yxmc,xnzymc,lqzymc,lqzy,byzxmc,byzxym,lqlbmc,tzsh,tjr,
case zsjj when 1 then '春季' else '秋季' end zsjj,xslymc,rxnf,zsss,jg,hkyszdz,rxzf,gkzf,fjf,fjflb,kstc,jlcc,tjjl,txdz,yzbm,CONVERT(VARCHAR(10),ZCRQ,120) AS ZCRQ,
(case when ZCRQ is not null then '是' else '否' end ) as ZCMC,CONVERT(VARCHAR(10),BDRQ,120) AS BDRQ,
(case when BDRQ is not null then '是' else '否' end ) as BDMC,CONVERT(VARCHAR(10),TXRQ,120) AS TXRQ,
(case when TXRQ is not null then '是' else '否' end ) as TXMC,
(case when isnull(d.zje,0)>0 then '已缴费' else '未缴费' end) as JFZT,BDCZR,ZCCZR,TXCZR
from V_ZS_SYXX a
left join (select jfqkmc,zkdjr,zkdjsj,ksh from V_ZS_ZKDJB) b on a.ksh = b.ksh
left join (select sfzsmc,rq,czr,ksh from V_ZS_ZSDJ) c on a.ksh = c.ksh
inner join (select c.XH,sum(b.JE) as zje from SF_SFDb c left join SF_SFDMX b on c.SFPJNM=b.SFPJNM group by c.XH) as d on d.XH = a.ksh