如何在没有游标的情况下优化此查询，我不想用光标，请拯救我的生命; （:(

本文介绍了如何在没有游标的情况下优化此查询，我不想用光标，请拯救我的生命; （:(的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 DECLARE cr CURSOR FAST_FORWARD FORSELECT [CardNo],[Time] FROM @DataORDER BY [Time]OPEN crFETCH NEXT FROM cr INTO @CardNo, @TimeWHILE (@@FETCH_STATUS = 0)BEGINSELECT CASE WHEN details.[EffectiveDate] = currentDetails.[CurrentEffectiveDate] THEN 1 ELSE 0 END AS IsCurrent,details.PersonnelBASeId, details.EffectiveDate, details.CardNoINTO ##tmpFROM tblPersonnelDetails AS details LEFT OUTER JOIN (SELECT PersonnelBASeId, MAX(EffectiveDate) AS CurrentEffectiveDate FROM tblPersonnelDetails WHERE (ISNULL(Deleted, 0) = 0) AND (EffectiveDate <= @Time) GROUP BY PersonnelBASeId) AS currentDetails ON details.PersonnelBASeId = currentDetails.PersonnelBASeIdWHERE (ISNULL(details.Deleted, 0) = 0) ORDER BY EffectiveDate DESC,PersonnelBaseId DESCENDENDCLOSE crDEALLOCATE cr 我尝试了什么： 在tsql中优化查询， i不要使用Cursor，它非常慢What I have tried:optimize query in tsql ,i dont want Use Cursor , Its very Slow推荐答案 SELECT CASE WHEN details.[EffectiveDate] = currentDetails.[CurrentEffectiveDate] THEN 1 ELSE 0 END AS IsCurrent, details.PersonnelBASeId, details.EffectiveDate, details.CardNoINTO ##tmpFROM tblPersonnelDetails AS details LEFT OUTER JOIN( SELECT PersonnelBASeId, MAX(EffectiveDate) AS CurrentEffectiveDate FROM tblPersonnelDetails WHERE (ISNULL(Deleted, 0) = 0) AND (EffectiveDate <= (SELECT MAX([Time]) FROM @Data)) GROUP BY PersonnelBASeId) AS currentDetails ON details.PersonnelBASeId = currentDetails.PersonnelBASeIdWHERE (ISNULL(details.Deleted, 0) = 0) ORDER BY EffectiveDate DESC,PersonnelBaseId DESC 但是据说这感觉有点好笑。如果您只想获取特定cardno的时间，只需将相关性添加到内部查询。But as said that feels a bit funny. If you intend to fetch only the times for a specific cardno, just add the correlation to the inner query. 这篇关于如何在没有游标的情况下优化此查询，我不想用光标，请拯救我的生命; （:(的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！