将列中的空值替换为条件中的零

Question

我不熟悉SQL Server数据库和查询。

我有一个包含DateTime和Current的SQL Server数据库表。Current可能具有空值。

仅当前一条记录或下一条记录中有一些值时，我才希望将当前列中的空值替换为零。提供的DateTime按升序排序。

请帮助我编写SQL查询或存储过程和SQL查询的组合。

还可以帮助我按升序使用DateTime对现有表进行排序。DateTime不是一个正在运行的系列。

​

​

Answer 1

您可以使用可更新的CTE和窗口函数：

with toupdate as (
      select t.*, lag(current) over (order by datetime) as prev_current,
             lead(current) over (order by datetime) as next_current
      from t
     ) 
update toupdate
    set current = 0
    where current is null and (prev_current is not null or next_current is not null);

如果您只想在select查询中增加一列(而不是更改数据)，那么：

with t as (
      select t.*, lag(current) over (order by datetime) as prev_current,
             lead(current) over (order by datetime) as next_current
      from t
     ) 
select t.*,
       (case when current is null and (prev_current is not null or next_current is not null)
             then 0 else current
        end) as new_current
from t;