Обновить нулевые значения по значению в одном столбце

У меня есть таблица в 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

Я хочу обновить нулевые значения, но не null в той же группе, с первым более высоким идентификатором, или если в одной группе больше нет, сначала ниже. Результат должен выглядеть следующим образом.

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

Спасибо!

Ответ 1

Вы можете использовать оконную версию функции SUM для определения островков NULL ценных записей вместе с записью с более высоким идентификатором в той же группе:

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

Демо здесь

Edit:

В приведенном выше запросе не обрабатывается край, где самая последняя запись в сегменте 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

Демо здесь

Ответ 2

Попробуйте это -

ОБРАЗОВАНИЕ ДАННЫХ

DECLARE @T TABLE
(
    GroupCd CHAR(1),
    Id INT,
    Value INT
)

INSERT INTO @T
VALUES('A',1,10),
('A',2,NULL),
('A',3,NULL),
('A',4,40),
('B',1,NULL),
('B',2,20),
('B',3,30),
('B',4,NULL)

Решение

UPDATE a
SET a.Value = b.Value
FROM @T a
INNER JOIN 
( 
    SELECT a.GroupCd,a.Id,Coalesce(a.Value,z.Value,z1.Value) Value 
    FROM @T a
    OUTER APPLY 
    (
        SELECT TOP 1 Value
        FROM @T b
        WHERE a.GroupCd = b.GroupCd
        AND b.Value IS NOT NULL AND a.Id < b.Id
        ORDER BY Id 
    )z
    OUTER APPLY 
    (
        SELECT TOP 1 Value
        FROM @T b
        WHERE a.GroupCd = b.GroupCd
        AND b.Value IS NOT NULL AND a.Id > b.Id
        ORDER BY Id DESC
    )z1
)b ON a.GroupCd = b.GroupCd AND a.Id = b.Id

SELECT * FROM @T

OUTPUT

GroupCd 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

(8 rows affected)

Ответ 3

Вы можете попробовать Этот простой метод

DECLARE @T TABLE
(
    GroupCd CHAR(1),
    Id INT,
    Value INT
)

INSERT INTO @T
VALUES('A',1,NULL),
('A',2,NULL),
('A',3,30),
('A',4,40),
('B',1,10),
('B',2,20),
('B',3,NULL),
('B',4,40)

SELECT
    *,
    NewVal = COALESCE(Value,(SELECT TOP 1 Value FROM @T WHERE GroupCd = T.GroupCd AND Id > T.Id AND Value IS NOT NULL ORDER BY Id ASC))
    FROM @T T

Мой результат

Ответ 4

update MY_TABLE set [value] = [newValue] from (
    select [Group] [newGroup],
           [Value] [newValue]
    from (
        select [Group], [Value],
               row_number() over (partition by [group] order by [Id] desc) [rn]
       from MY_TABLE
       where [Value] is not null
    ) [a] where [rn] = 1
) where [Group] = [newGroup] and [Value] is null