Server( top )从botom到top更新表

Question

我试图从下到上更新表临时(F4)结果。每一行和更新都可以按顺序循环吗？

​

​

谢谢!

Answer 1

假设ROW是正整数，那么您可以使用(演示)

WITH T1 AS
(
SELECT *, 
       MIN(CASE WHEN F4 <> 0 THEN CONCAT(FORMAT([ROW],'D10'),[F4]) END) 
         OVER (ORDER BY [ROW] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS M
FROM temp

)
SELECT *, 
       CAST(SUBSTRING(M,11,11) AS INT) AS val
FROM T1
ORDER BY [ROW]

如果您想更新F4，可以使用

WITH T1 AS
(
SELECT *, 
      MIN(CASE WHEN F4 <> 0 THEN CONCAT(FORMAT([ROW],'D10'),[F4]) END) 
           OVER (ORDER BY [ROW] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS M
FROM temp

)
UPDATE T1
SET F4 = COALESCE(SUBSTRING(M,11,11),0);