SQL将多行中的字段的空值填充为先前的非空值 [英] SQL fill null values for a field in multiple rows as previous non null value

问题描述

我在netezza中有一个表(基于postgresql)，如下所示.我需要用name的NULL值创建一个新表，以替换上一个非空行的name值.

I have a table in netezza (based on postgresql) like below. I need to create a new table with the NULL values for name to be replaced with value of name for the previous non-null row.

id name time    value
---------------------
1 john  11:00   324
2 NULL  12:00   645
3 NULL  13:00   324
4 bane  11:00   132
5 NULL  12:00   30
6 NULL  13:00   NULL
7 NULL  14:00   -1
8 zane  11:00   152
9 NULL  12:00   60
10 NULL 13:00   NULL

输出表

name    time    value
---------------------
john    11:00   324
john    12:00   645
john    13:00   324
bane    11:00   132
bane    12:00   30
bane    13:00   NULL
bane    14:00   -1
zane    11:00   152
zane    12:00   60
zane    13:00   NULL

笔记:

1. 由于权限限制，无法更改table1，因此创建了一个新表 是这样.

1. Cannot alter table1 due to permission restrictions, so a new table is the way.

需要在Netezza(最好是)或MS Access中运行它.

Need to run this in Netezza (preferably) or MS Access.

用于在Netezza中创建测试数据的代码如下.

Code used to create the test data in Netezza is as below.

create temp table test (
    id int
    ,name varchar(10)
    ,time time
    ,value int
)distribute on random;

insert into test (id, name, time, value) values(1, 'joe', '10:00', 324);
insert into test (id, name, time, value) values(2, null, '11:00', 645);
insert into test (id, name, time, value) values(3, null, '12:00', 324);

insert into test (id, name, time, value) values(4, 'bane', '10:00', 132);
insert into test (id, name, time, value) values(5, null, '11:00', 30);
insert into test (id, name, time, value) values(6, null, '12:00', null);
insert into test (id, name, time, value) values(7, null, '13:00', -1);

insert into test (id, name, time, value) values(8, 'zane', '10:00', 152);
insert into test (id, name, time, value) values(9, null, '11:00', 60);
insert into test (id, name, time, value) values(10, null, '12:00', null);

推荐答案

这在Access 2010中对我有效:

This works for me in Access 2010:

SELECT 
    t1.id,
    (
        SELECT TOP 1 t2.name
        FROM test t2
        WHERE t2.id<=t1.id AND t2.name IS NOT NULL
        ORDER BY t2.id DESC
    ) AS name,
    t1.time,
    t1.value
FROM test t1

它也可以在其他SQL语言中使用，尽管它们使用TOP 1的方式可能略有不同(例如，LIMIT 1是常见的变体).

It should work in other SQL dialects as well, although they may have a slightly different way of doing TOP 1 (e.g., LIMIT 1 is a common variant).

这篇关于SQL将多行中的字段的空值填充为先前的非空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！