sql - 根据行号选择 ID
问题描述
这是我的桌子
| | Id |
|1 |07A83EA7-6E00-4265-906C-FF187FE18EB3|
|2 |B6C10F47-7DEE-4D2B-AA3D-FF27B8FC105C|
|3 |10F56030-0803-4133-B57B-FF3F941A0D60|
是否可以选择Id
基于行号?
我试过了:
SELECT [Id],ROW_NUMBER() OVER(Order by Id ASC) AS RowLol, Id
但这对于我的意图并没有真正的功能,因为它会生成一个临时字段。
所需功能:
能够遍历所有条目,并将其用作UPDATE
循环中 my 的参数
--DECLARE @json NVARCHAR(MAX) = (SELECT Data FROM ProcessEventMessages WHERE Id='AEF11F7B-06C1-4C40-9806-0004CAC6A042')
-- This will only work for ONE instance. Hence why i need an iterative ID, to pass along 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;
--loop on id
DECLARE @loopCounter INT = 0;
WHILE @loopCounter < (SELECT count(Id) FROM ProcessEventMessages)
BEGIN
PRINT 'changing values...';
UPDATE ProcessEventMessages SET Data = JSON_MODIFY(@json,'$.time', FORMAT(DATEADD(hour,-2,JSON_VALUE(@json,'$.time')),'yyyy-MM-ddTHH:mm:ss.fff')) WHERE ROWNUMBER = @loopCounter)
SET @loopCounter = @loopCounter + 1;
END;
PRINT 'done';
GO
建议:
DECLARE @json NVARCHAR(MAX)
DECLARE @loopCounter INT = 0;
WHILE @loopCounter < (SELECT count(Id) FROM ProcessEventMessages)
BEGIN
PRINT 'Changing...';
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
尝试崩溃:
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) -- RowNo isn't declared here. Where would i move it?
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
例子:
DECLARE @RowNrr int = (SELECT *,ROW_NUMBER() OVER(Order by Id ASC) AS RowNrr FROM ProcessEventMessages)
SET @json = (SELECT Data FROM ProcessEventMessages WHERE @RowNrr = @loopCounter)
...
and then
...
;WITH CTE AS(
SELECT @RowNrr
)
but it doesn't work :/
解决方案
假设您使用 sql server 作为 dbms,现在如果我正确理解了您的要求,那么您可能会寻找CTE,
;WITH CTE AS(
SELECT [Id],
ROW_NUMBER() OVER(Order by Id ASC) AS RowNo
FROM TableName
)
SELECT ID
FROM CTE
WHERE RowNo IN(1,2,3)
更新
如果您尝试将更新命令与 cte 表单集成,那么这可能看起来像这样。
;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
注意:我不确定 id 上的行号是否一致。因为如果插入了新 ID,那么行号可能会发生变化。
推荐阅读
- sql - 如何在线程中异步执行大量 sql 查询
- python - PyPDF2 PdfFileMerger在合并文件中丢失PDF模块
- arduino - Arduino:LCD不会关闭
- composer-php - 如何使用分叉存储库添加多个包?
- excel - OFFSET 函数忽略范围内的空白单元格?
- ruby-on-rails - 在 Rails 多表查询中避免 N+1 查询
- android - 清单合并失败:uses-sdk:minSdkVersion 15 不能小于库中声明的版本 16
- tinymce - TinyMCE 自动保存插件和 localStorage
- jquery - 如何使用带有日期选择器的 jQuery 使文本出现在另一个文本框中?
- android - Dagger2 和协程如何结合?