如何将空值替换为下一行的值

Question

我需要在我的SQL查询代码中获得支持。我必须用下一行的非空值替换列中的空值。

我们可以使用以下代码作为示例：

declare   @value table (r# int, value varchar(15))
insert into @value ( r#, value ) values
 (1, NULL   ) ,
 (2, 'January'), 
 (3, 'February' ), 
 (4, NULL    ),
 (5, 'March'  ),
 (6, NULL    ),
(7, Null  ),
(8, 'December' ),
(9, Null ),
(10, Null  ),
(11, Null  ),
(12, 'November' ),
(13, Null )
select * from @value

当我使用LEAD函数时，我得到了这个值，但是它不能处理NULL值。 我需要的是：

1 January
2 January
3 February
4 March
5 March
6 December
7 December
8 December
9 November
10 November
11 November
12 November
13 NULL

SELECT r#, 
  value
 ,case when value is null  then Lead(value) OVER ( order by  r#  asc) else value end as RESULT 
FROM @value
order by r#

我有：

- LordDevPath

---

Next - 根据什么？ - jarlh

你使用哪个数据库管理系统？（SQL Server？） - jarlh

是的，SQL Server。在列值中，我有空值，并且我想用下一行（来自同一列）的值替换它们，例如对于第1行，我想要1月的值，对于第9、10和11行，我想要11月的值。 - LordDevPath

我使用lead函数：请通过编辑问题包含此代码-否则我们无法猜测您如何解决问题。 - Richard

你的问题在于LEAD获取了下一个值。这个值可能为空（在你的数据中，对于只有一个空值的一月份可以工作，但是对于需要获取下下行的十二月份则不行）。LEAD没有能力“获取满足条件的下一行”——为此，你需要某种形式的连接或内部查询（答案中有几个选项）。 - Richard

Answer 1

对于所有支持的版本：

您可以使用额外的APPLY运算符来查找第一个具有非NULL值的记录：

T-SQL:

SELECT 
   v1.[r#], 
   COALESCE(v1.[value], v2.[value]) AS [value]
FROM @value v1
OUTER APPLY (
   SELECT TOP 1 [Value]
   FROM @value 
   WHERE (v1.[r#] < [r#]) AND [value] IS NOT NULL
) v2

输出：

r#  value
1   January
2   January
3   February
4   March
5   March
6   December
7   December
8   December
9   November
10  November
11  November
12  November
13  NULL

针对 SQL Server 2022+：

从 SQL Server 2022+ 开始，您可以尝试使用未记录的 IGNORE NULLS 选项来使用 LEAD() 函数：

SELECT 
   r#, 
   COALESCE([value], LEAD([value]) IGNORE NULLS OVER (ORDER BY r#)) AS [value]
FROM @value
ORDER BY r#

- Zhorov

---

该CASE语句作为 COALESCE(v1.[value], v2.[value]) AS [value] 会更简洁。 - Andy Nichols

这看起来像是最后一个非NULL谜题，Itzik Ben-Gan提出了非常高效的解决方案。 - Vladimir Baranov

Answer 2

你可以使用窗口函数来实现这一点。不幸的是，SQL Server不支持在LEAD()中使用IGNORE NULL选项，因此这不是一个选项。但是，你可以使用两层窗口函数：

select v.r#, v.value,
       coalesce(v.value, max(value) over (partition by next_r#)) as imputed_value
from (select v.*,
             min(case when value is not null then r# end) over (order by r# desc) as next_r#
      from @value v
     ) v
order by v.r#;

在13行中，性能差异可能不会引起注意。 然而，随着行数增加，这应该有更好的性能。

- Gordon Linoff

---

@PatrycjaKowalczyk . . . 你为什么取消了这个答案？ - Gordon Linoff

Answer 3

你可以尝试使用相关子查询来运行以下SQL代码。

SELECT v1.r#,CASE WHEN value IS NOT NULL THEN v1.Value     
             ELSE ( SELECT TOP 1 value
                    FROM @value  v2
                    WHERE v2.r# > v1.r# AND v2.[value] IS NOT NULL
                  ) END  Value 
FROM @value v1

OR (不使用 Case 语句)

SELECT v1.r#,ISNULL(v1.VALUE,( SELECT TOP 1 value
                            FROM @value  v2
                            WHERE v2.r# > v1.r# 
                            AND v2.[value] IS NOT NULL
                           ) 
                      ) AS [MonthNames]
FROM @value v1

结果

r#  Value
----------
1   January
2   January
3   February
4   March
5   March
6   December
7   December
8   December
9   November
10  November
11  November
12  November
13  NULL

- Sreenu131

Answer 4

以下查询在SQL Server中有效：

;WITH CTE_Value
AS (
    SELECT R#, Value
    FROM @value AS T
    WHERE Value IS NOT NULL

    UNION ALL

    SELECT t.r#, c.Value
    FROM @value AS t
    INNER JOIN CTE_Value AS c ON t.r# + 1 = c.r#
    WHERE t.Value IS NULL
    )
SELECT *
FROM CTE_Value

UNION ALL

SELECT v.*
FROM @value AS v
LEFT JOIN CTE_value AS c ON v.r# = c.r#
WHERE c.r# IS NULL
ORDER BY r#

- Venkataraman R