SQL Server嵌套JSON动态字段求最大值方案异常排查

问题根因 #

你的代码错误出在获取ID上下限的语句上：

SELECT @max_ID = MAX(ID),@min_ID = MIN(ID) FROM @col_table GROUP BY ColumnName;

你额外加了GROUP BY ColumnName，而@col_table里存在两个不同的列名（AEDAT、ERDAT），这条查询会返回两行结果。SQL Server中用查询给变量赋值时如果返回多行，变量只会保留最后一行的赋值结果。执行完这条语句后，你的@min_ID和@max_ID都会被赋值为2，因此循环只会执行1次，仅插入了ERDAT对应的5条数据，最终结果自然只有5条。

修复方案 #

简易修复 #

直接去掉上述语句中的GROUP BY ColumnName即可：

SELECT @max_ID = MAX(ID),@min_ID = MIN(ID) FROM @col_table;

修改后@min_ID为1、@max_ID为2，循环会执行两次，就能插入AEDAT、ERDAT对应的共10条数据，达到你要的UNION ALL效果。

更优方案（无需循环） #

你可以用动态SQL直接拼接你需要的查询语句，执行效率更高，逻辑也更简洁：

DECLARE @sql nvarchar(max) = N''
SELECT @sql = @sql + N' UNION ALL SELECT JSON_VALUE(j.value,''$.' + ColumnName + N''') FROM OPENJSON(@json,''$.value'') AS j'
FROM @col_table

-- 去掉开头多余的UNION ALL
SET @sql = STUFF(@sql, 1, 10, N'')

-- 执行动态SQL
INSERT INTO @max_val (LastUpdatedValue)
EXEC sp_executesql @sql, N'@json nvarchar(max)', @json = @json

内容的提问来源于stack exchange，提问作者pythondumb