SQL Server 用最后一个值替换 NULL

2021-09-25 00:00:00 sql window-functions sql-server

我想找到一种用最后一个非空值替换空值的方法.我有一张像:

I want to find a way to replace NULL values by the last not NULL value. I have a table like :

Date        Cost
2017-01-01  18.6046511
2017-01-03  22.9787234
2017-01-03  NULL
2017-01-12  18.8584937
2017-01-16  19.1827852
2017-01-16  NULL
2017-01-19  NULL
2017-02-21  NULL
2017-03-04  24.0597622
2017-03-28  NULL
2017-04-17  33.5398414
2017-04-17  NULL

我想用最后一个非空值替换空值所以结果会是这样的:

I want to replace NULL value by the last not NULL value so the result will be like:

Date        Cost
2017-01-01  18.6046511
2017-01-03  22.9787234
2017-01-03  22.9787234
2017-01-12  18.8584937
2017-01-16  19.1827852
2017-01-16  19.1827852
2017-01-19  19.1827852
2017-02-21  19.1827852
2017-03-04  24.0597622
2017-03-28  24.0597622
2017-04-17  33.5398414
2017-04-17  33.5398414

推荐答案

您可以通过使用窗口框架 (GUWF) 创建组来尝试以下查询.源链接.

You can try the following query by creating a Group Using Window Frame (GUWF). source link.

create table MyTable ([dtDate] date, Cost decimal(18, 6))
insert into MyTable values 
('2017-01-01',  18.6046511),
('2017-01-03',  22.9787234),
('2017-01-03',  NULL),
('2017-01-12',  18.8584937),
('2017-01-16',  19.1827852),
('2017-01-16',  NULL),
('2017-01-19',  NULL),
('2017-02-21',  NULL),
('2017-03-04',  24.0597622),
('2017-03-28',  NULL),
('2017-04-17',  33.5398414),
('2017-04-17',  NULL)

SELECT dtDate, Cost = MAX(Cost) OVER (PARTITION BY c)
FROM
(
    SELECT dtDate, Cost
        ,c = count(Cost) OVER (ORDER BY dtDate)
    FROM MyTable
) a
ORDER BY dtDate;

现场演示

相关文章