迭代循环来选择行(iterative loop to select row)

迭代循环来选择行(iterative loop to select row)

我需要能够选择一个特定的行，因为SET只保存一行。

在第5行，我必须声明@json，以便OPENJSON可以工作。

这就是为什么我需要查询中的RowNo。 但是，在这一点上， RowNo没有定义。

我将如何去解决这个问题？ 看起来我无论如何旋转它都没有关系，事情总是会变得没有定义。 我如何根据需要指向第5 row的特定row ？

DECLARE @loopCounter INT = 0; WHILE @loopCounter < (SELECT count(Id) FROM ProcessEventMessages) BEGIN PRINT 'Changing...'; DECLARE @json NVARCHAR(MAX) = (SELECT Data FROM ProcessEventMessages WHERE RowNo = @loopCounter) -- here SELECT * FROM OPENJSON(@json) WITH ( message varchar(200) '$.message', machineId varchar(200) '$.machineId', machineName int '$.machineName', ipAddress varchar(200) '$.ipAddress', LocalTime datetime2(7) '$.time' ) AS ChangeTime; ;WITH CTE AS( SELECT *,ROW_NUMBER() OVER(Order by Id ASC) AS RowNo FROM ProcessEventMessages ) UPDATE CTE SET Data = JSON_MODIFY(@json,'$.time', FORMAT(DATEADD(hour,-2,JSON_VALUE(@json,'$.time')),'yyyy-MM-ddTHH:mm:ss.fff')) WHERE RowNo = @loopCounter SET @loopCounter = @loopCounter + 1; END; PRINT 'Done'; GO

I need to be able to SELECT a specific row because SET only holds one row.

On line 5, I have to declare @json so that OPENJSON can work.

And that's why i need the RowNo at this point in the query. However, at this point, RowNo is not defined.

How would i go about fixing this problem? It seems that it doesn't matter how i spin it, something is always gonna be undefined. How would i point to a specific row on line 5 as desired?

DECLARE @loopCounter INT = 0; WHILE @loopCounter < (SELECT count(Id) FROM ProcessEventMessages) BEGIN PRINT 'Changing...'; DECLARE @json NVARCHAR(MAX) = (SELECT Data FROM ProcessEventMessages WHERE RowNo = @loopCounter) -- here SELECT * FROM OPENJSON(@json) WITH ( message varchar(200) '$.message', machineId varchar(200) '$.machineId', machineName int '$.machineName', ipAddress varchar(200) '$.ipAddress', LocalTime datetime2(7) '$.time' ) AS ChangeTime; ;WITH CTE AS( SELECT *,ROW_NUMBER() OVER(Order by Id ASC) AS RowNo FROM ProcessEventMessages ) UPDATE CTE SET Data = JSON_MODIFY(@json,'$.time', FORMAT(DATEADD(hour,-2,JSON_VALUE(@json,'$.time')),'yyyy-MM-ddTHH:mm:ss.fff')) WHERE RowNo = @loopCounter SET @loopCounter = @loopCounter + 1; END; PRINT 'Done'; GO

最满意答案

您可以使用CROSS APPLY而不是循环。 尝试：

WITH CTE AS ( SELECT LocalTime , Data FROM dbo.ProcessEventMessages CROSS APPLY OPENJSON(Data) WITH ( LocalTime datetime2(7) '$.time' ) AS ChangeTime ) UPDATE CTE SET Data = JSON_MODIFY(Data,'$.time', FORMAT(DATEADD(hour,-2,LocalTime),'yyyy-MM-ddTHH:mm:ss.fff'));

You can use CROSS APPLY instead of a loop. Try:

WITH CTE AS ( SELECT LocalTime , Data FROM dbo.ProcessEventMessages CROSS APPLY OPENJSON(Data) WITH ( LocalTime datetime2(7) '$.time' ) AS ChangeTime ) UPDATE CTE SET Data = JSON_MODIFY(Data,'$.time', FORMAT(DATEADD(hour,-2,LocalTime),'yyyy-MM-ddTHH:mm:ss.fff'));