SQL 循环遍历结果和更新值

Question

我正在尝试找到一种更有效的方法来循环我的表，该方法不会造成这样的瓶颈，根据前几行中的结果更新行，这些行可能已经或可能不会已经由相同的逻辑更新

。

目前，我正在使用以下代码来循环访问我的表。 示例结果的图片如下。

基本上，如果逻辑看到步骤 8 并看到步骤 10 或 11 发生在此步骤之前，则将此步骤重新编号为步骤 14。

我不确定光标是否会更好地工作。 该表已编制索引，根据数据范围，该表将有超过一百万行数据，希望根据 20 个不同的 ProcessID 评估此逻辑。

谢谢你的帮助！

DECLARE @RowsToProcess55  int
DECLARE @CurrentRow55     int
DECLARE @SelectCol155     int
DECLARE @table155 TABLE (RowID int not null primary key identity(1,1), col1 int )  
INSERT into @table155 (col1) SELECT ID FROM ##HEFStepRenumber
SET @RowsToProcess55=@@ROWCOUNT
SET @CurrentRow55=0
WHILE @CurrentRow55<@RowsToProcess55
BEGIN
SET @CurrentRow55=@CurrentRow55+1
SELECT 
    @SelectCol155=col1
    FROM @table155
    WHERE RowID=@CurrentRow55
Update  a
Set StepIDNew = (Select Case 
when a.ProcessID = 1 and a.StepID = 9 and (Select distinct StepID from ##HEFStepRenumber as b where b.EventID = a.EventID and b.UserDispName = a.UserDispName and b.Dates = a.Dates and b.TriggerLogID < a.TriggerLogID and StepID = 30)=30 then 31 
when a.ProcessID = 1 and a.StepID = 8 and (Select distinct StepID from ##HEFStepRenumber as b where b.EventID = a.EventID and b.UserDispName = a.UserDispName and b.Dates = a.Dates and b.TriggerLogID < a.TriggerLogID and StepID = 25)=25 then 26
when a.ProcessID = 1 and a.StepID = 4 and a.TriggerDisplayName <> 'MS Adjud_Open Doc Hub' and (Select distinct StepID from ##HEFStepRenumber as b where b.EventID = a.EventID and b.UserDispName = a.UserDispName and b.Dates = a.Dates and b.TriggerLogID < a.TriggerLogID and b.TriggerDisplayName <> 'MS Adjud_Open Doc Hub' and StepID = 20)=20 then 24
when a.ProcessID = 1 and a.StepID = 9 and (Select distinct StepID from ##HEFStepRenumber as b where b.EventID = a.EventID and b.UserDispName = a.UserDispName and b.Dates = a.Dates and b.TriggerLogID < a.TriggerLogID and StepID = 20)=20 then 23
when a.ProcessID = 1 and a.StepID = 8 and (Select distinct StepID from ##HEFStepRenumber as b where b.EventID = a.EventID and b.UserDispName = a.UserDispName and b.Dates = a.Dates and b.TriggerLogID < a.TriggerLogID and StepID = 20)=20 then 22
when a.ProcessID = 1 and a.StepID = 4 and a.TriggerDisplayName <> 'MS Adjud_Open Doc Hub' and  (Select count(distinct StepIDNew) from ##HEFStepRenumber as b where b.EventID = a.EventID and b.UserDispName = a.UserDispName and b.Dates = a.Dates and b.TriggerLogID < a.TriggerLogID and b.TriggerDisplayName <> 'MS Adjud_Open Doc Hub' and StepIDNew in(13,16,17))>=1 then 21
when a.ProcessID = 1 and a.StepID = 5 and (Select distinct StepIDNew from ##HEFStepRenumber as b where b.EventID = a.EventID and b.UserDispName = a.UserDispName and b.Dates = a.Dates and b.TriggerLogID < a.TriggerLogID and StepIDNew = 13)=13 then 17
when a.ProcessID = 1 and a.StepID = 10 and (Select distinct StepIDNew from ##HEFStepRenumber as b where b.EventID = a.EventID and b.UserDispName = a.UserDispName and b.Dates = a.Dates and b.TriggerLogID < a.TriggerLogID and StepIDNew = 13)=13 then 16
when a.ProcessID = 1 and a.StepID = 9 and (Select count(distinct StepID) from ##HEFStepRenumber as b where b.EventID = a.EventID and b.UserDispName = a.UserDispName and b.Dates = a.Dates and b.TriggerLogID < a.TriggerLogID and StepID in (10,11))>=1 then 15
when a.ProcessID = 1 and a.StepID = 8 and (Select count(distinct StepID) from ##HEFStepRenumber as b where b.EventID = a.EventID and b.UserDispName = a.UserDispName and b.Dates = a.Dates and b.TriggerLogID < a.TriggerLogID and StepID in (10,11))>=1 then 14
when a.ProcessID = 1 and a.StepID = 4 and a.TriggerDisplayName <>'MS Adjud_Open Doc Hub' and (Select count(distinct StepID) from ##HEFStepRenumber as b where b.EventID = a.EventID and b.UserDispName = a.UserDispName and b.Dates = a.Dates and b.TriggerLogID < a.TriggerLogID and StepID in(10,11) and b.TriggerDisplayName <> 'MS Adjud_Open Doc Hub')>=1 then 13
else a.StepID end)
From ##HEFStepRenumber a
where a.ID = @CurrentRow55
END

示例结果

Answer 1

没有足够的点来评论所以

如果您的查询案例语句怎么办

SELECT DISTINCT StepID
FROM ##HEFStepRenumber AS b
WHERE b.EventID = a.EventID
    AND b.UserDispName = a.UserDispName
    AND b.Dates = a.Dates
    AND b.TriggerLogID < a.TriggerLogID
    AND StepID = 30

返回超过 1 个值？