sql-server - 按同一列中的值更新空值

Question

我在 MS SQL Server 中有一个表，其中“值”列中有一些空值

Group   ID    Value
A       1     10
A       2     
A       3     
A       4     40
B       1     
B       2     20
B       3     30
B       4          

我想用第一个较高 ID 的同一组中的 not null 来更新 null 值，或者如果同一组中没有更高的 ID，则先较低的值。所以结果应该是这样的。

Group   ID    Value
A       1     10
A       2     40
A       3     40
A       4     40
B       1     20
B       2     20
B       3     30
B       4     30 

谢谢!

Answer 1

您可以使用 SUM 函数的窗口版本来确定 NULL 值记录岛以及同一组中具有较高 ID 的记录:

SELECT [Group], ID, Value, 
       SUM(CASE WHEN Value IS NULL THEN 0 ELSE 1 END) OVER 
       (PARTITION BY [Group] ORDER BY ID DESC) AS grp
FROM mytable

输出:

Group   ID  Value   grp
-----------------------
A       4   40      1
A       3   30      2
A       2   NULL    2
A       1   NULL    2
B       4   40      1
B       3   NULL    1
B       2   20      2
B       1   10      3

您现在可以将上述查询包装在 CTE 中，并使用另一个 CTE 进行更新:

;WITH CTE AS (
SELECT [Group], ID, Value, 
       SUM(CASE WHEN Value IS NULL THEN 0 ELSE 1 END) OVER 
       (PARTITION BY [Group] ORDER BY ID DESC) AS grp
FROM mytable
), ToUpdate AS (
   SELECT [Group], ID, Value,  
          MAX(Value) OVER (PARTITION BY [Group], grp) AS group_value  
   FROM CTE
)
UPDATE ToUpdate
SET Value = group_value
WHERE Value IS NULL

Demo here

编辑:

上述查询不处理 Group 切片中最后记录为 NULL 的边缘情况。要处理这种情况，您可以使用以下查询:

;WITH CTE AS (
SELECT [Group], ID, Value, 
       SUM(CASE WHEN Value IS NULL THEN 0 ELSE 1 END) OVER 
       (PARTITION BY [Group] ORDER BY ID DESC) AS grp,
       SUM(CASE WHEN Value IS NULL THEN 0 ELSE 1 END) OVER 
       (PARTITION BY [Group] ORDER BY ID) AS grp2
FROM mytable
), ToUpdate AS (
   SELECT [Group], ID, Value,            
          MAX(Value) OVER (PARTITION BY [Group], grp) AS group_value,  
          MAX(Value) OVER (PARTITION BY [Group], grp2) AS group_value2  
   FROM CTE
)
UPDATE ToUpdate
SET Value = COALESCE(group_value, group_value2)
WHERE Value IS NULL

Demo here