select KDBH, jgclass,
CheckResult = (select top 1 CheckResult from CheckList where KDBH =ww.KDBH and jgclass=ww.jgclass order by CheckTime desc ),
CheckID= (select top 1 CheckID from CheckList where KDBH =ww.KDBH and jgclass=ww.jgclass order by CheckTime desc )
from CheckList ww where datediff(d,CheckTime,getdate())<7 group by KDBH,jgclass现在执行需要30秒,希望大家给出优化意见
CheckResult = (select top 1 CheckResult from CheckList where KDBH =ww.KDBH and jgclass=ww.jgclass order by CheckTime desc ),
CheckID= (select top 1 CheckID from CheckList where KDBH =ww.KDBH and jgclass=ww.jgclass order by CheckTime desc )
from CheckList ww where datediff(d,CheckTime,getdate())<7 group by KDBH,jgclass现在执行需要30秒,希望大家给出优化意见
select a.KDBH,a.jgclass,b.CheckResult,b.CheckID
from CheckList a left join CheckList b on a.KDBH =b.KDBH and a.jgclass=b.jgclass
where datediff(dd,a.CheckTime,getdate())<7
and not exists(select 1 from CheckList where KDBH = b.KDBH and jgclass = b.jgclass and CheckTime > b.CheckTime)--试试看!
ww.jgclass ,
CheckResult = T.CheckResult ,
CheckID = T.CheckID
FROM CheckList ww
INNER JOIN ( SELECT TOP 1
CheckID ,
CheckResult ,
KDBH ,
jgclass
FROM CheckList
ORDER BY CheckTime DESC
) T ON T.KDBH = ww.KDBH
AND T.jgclass = ww.jgclass
WHERE DATEDIFF(dd, CheckTime, GETDATE()) < 7
GROUP BY ww.KDBH ,
ww.jgclass ,
T.CheckResult ,
T.CheckIDKDBH,jgclass 上加索引,然后Try
CheckResult = (select top 1 CheckResult from CheckList where KDBH =ww.KDBH and jgclass=ww.jgclass
order by CheckTime desc ),
CheckID= (select top 1 CheckID from CheckList where KDBH =ww.KDBH and jgclass=ww.jgclass
order by CheckTime desc )
from #tb a
drop table #tb
CheckResult, CheckID
from CheckList ww where CheckTime <getdate()-7
and checktime=(select max(checktime) from CheckList where KDBH=ww.KDBH and jgclass=ww.jgclass)--根据你的数据结构建立适合索引