下面的这个查询语句写的不是很理想，查询六千多条数据要3秒多钟，想请各位帮我优化一下，谢谢了！
select 'x' as pjlx,lszh,spdm
,dwdm=(case when substring(dwdm,1,3)='YF' then 'AA-23' else dwdm end)
,dwmc,mxjls,xcljls,kcsjls,zhsmsj
,zfbz=(case when minclbz<-1 then 'y' else 'n' end)
,xclbz=(case when xcljls>0 then 'y' else 'n' end)
,kcsbz=(case when smlxs=1 and kcsjls>0 and kcsjls=xcljls then 'y' else 'n' end)
,smlx=(case when smlxs=1 and maxsmlx in ('销售出库','退货入库','抽检出库','销毁出库')then maxsmlx else '待定' end)
from(
select lszh,spdm,dwdm,dwmc,max(smsj) as zhsmsj,max(isnull(smlx,'')) as maxsmlx
,min(isnull(clbz,0)) as minclbz,count(*) as mxjls
,count(case when isnull(clbz,0) in (0,1,2) then lszh else null end) as xcljls
,count(case when isnull(clbz,0)=2 and isnull(smlx,'')in('销售出库','退货入库','抽检出库','销毁出库') then lszh else null end) as kcsjls
,count(distinct (case when isnull(clbz,0)<0 then null else isnull(smlx,'') end)) as smlxs
from lspy_jj_wlspy with(nolock)
where lszh not in (
select lszh from (
select distinct lszh,spdm,dwdm,dwmc
,kzbz=(case when isnull(spdm,'')='' or isnull(dwdm,'')='' or isnull(dwmc,'')='' then 1 else 0 end)
from lspy_jj_wlspy with(nolock) where isnull(clbz,0)>=0
)x
group by lszh having (count(*)>1 or max(kzbz)=1)
)
group by lszh,spdm,dwdm,dwmc having max(smsj)<convert(varchar(10),getdate(),126)
)y
执行计划太大了，贴不下，我就不贴了，反正是很复杂，看了就想发火的那种。谢谢大家！
sql 查询 优化
------解决方案--------------------
给数据和要得到的结果吧...
------解决方案--------------------
子查询嵌套的太多了，楼主你想办法给分开吧
------解决方案--------------------
用临时表 把查询拆分开来实现。not in部分可以改写成not exists 然后就是注意索引的设计了
------解决方案--------------------
过滤字段建索引试试
------解决方案--------------------
验证一下数据，尽量少用isnull函数
像版主说的，太复杂的查询可以借用临时表拆分，也可以给临时表加索引
用完删除临时表就行了
------解决方案--------------------
正所谓纸上得来终觉浅，绝知此事要躬行。just do it。你只是运气不好，遇到一个烂人写的懒程序而已。------解决方案--------------------
根据业务需求重整吧
not in 和子查询分组统计那里数据库比较多的情况下估计是有问题
貌似只查询了一个表，搞清楚需求，自己重整一个吧，尝试建好索引，应该能快起来
------解决方案--------------------