sql - 如何进一步优化此 SQL 代码以更快地运行

Question

UPDATE N SET [actType] = 'X'
FROM tableA N
WHERE NOT EXISTS (SELECT 1  
                  FROM tableA O 
                  WHERE O.clientCode = N.clientCode AND 
                        O.[userName] = N.[userName] AND 
                        O.[profile] = N.[profile] AND 
                        O.[rankID] = N.[rankID] - 1 
                 ) AND 
      N.[rankID] NOT IN (SELECT MIN(T.[rankID]) 
                         FROM tableA T 
                         WHERE T.[userName] = N.[userName]
                        )

标签： sqlsql-optimization

Answer 1

您可以使用row_number()可更新的cte：

with cte as (
     select N.*, ROW_NUMBER() OVER (PARTITION BY [userName] ORDER BY rankID) AS Seq
     from tableA N
)
update c
     set c.[actType] = 'X'
from cte c
where seq > 1 and
      not exists (SELECT 1  
                  FROM tableA O 
                  WHERE O.clientCode = c.clientCode AND 
                        O.[userName] = c.[userName] AND 
                        O.[profile] = c.[profile] AND 
                        O.[rankID] = c.[rankID] - 1 
                );

您的语法建议SQL Server. 所以，你可以使用。