以下access数据库语句怎么优化，效率在数据数为3000时，执行很差

[潜龙在渊]

以下access数据库语句怎么优化，效率在数据数为3000时，执行很差
SELECT top 10 jobid,classid,jobname,provincecode,citycode,peaplenum,publishtime,remark,(SELECT count(jobid) FROM t_jobs WHERE isdelete=0 and ( jobname like '%销%') ) as totalcount,(select name from t_classify where t_jobs.classid=t_classify.classid and t_classify.isdelete=0) AS classname FROM t_jobs WHERE isdelete=0 and ( jobname like '%销%') and jobid not in (select top 50 jobid from t_jobs where isdelete=0 and ( jobname like '%销%') order by jobid desc ) order by jobid desc

[silwol]

(select name from t_classify where t_jobs.classid=t_classify.classid and t_classify.isdelete=0) AS classname --->可以改成left join 其他的我大概看了下，你这个除了totalcount外，就是取isdelete=0 and ( jobname like '%销%')的jobid倒序51-60这10个。 不清楚access有没有ROW_NUMBER()，没有的话你可以考虑建一张带有自增列的临时表#temp(id,...)

insert into #temp(...)
select jobid,classid,jobname,provincecode,citycode,peaplenum,publishtime,remark,name)
from t_jobs a
left join t_classify b
  on a.classid=b.classid and b.isdelete=0
where a.isdelete=0 and (a.jobname like '%销%')
order by a.jobid desc

#temp里面的id (51-60)就是你要的，count(1)就是totalcount