SQL查询将行中的空值替换为来自上一个已知值的值

如果您使用的是SQL Server,这应该可以工作

DECLARE @Table TABLE(
        ID INT,
        Val INT
)

INSERT INTO @Table (ID,Val) SELECT 1, 3
INSERT INTO @Table (ID,Val) SELECT 2, NULL
INSERT INTO @Table (ID,Val) SELECT 3, 5
INSERT INTO @Table (ID,Val) SELECT 4, NULL
INSERT INTO @Table (ID,Val) SELECT 5, NULL
INSERT INTO @Table (ID,Val) SELECT 6, 2


SELECT  *,
        ISNULL(Val, (SELECT TOP 1 Val FROM @Table WHERE ID < t.ID AND Val IS NOT NULL ORDER BY ID DESC))
FROM    @Table t

下面是一个mysql解决方案:

UPDATE mytable
SET number = (@n := COALESCE(number, @n))
ORDER BY date;

这是简洁的,但不需要在其他品牌的rdbms中工作。对于其他品牌,可能会有一个与品牌相关的解决方案。这就是为什么告诉我们你使用的品牌很重要。

正如@pax所评论的那样,独立于供应商是很好的,但是如果做不到这一点,那么充分利用您选择的数据库品牌也是很好的。

对上述问题的解释:

@n 是一个mysql用户变量。它以空开始,并在更新通过行时为每行分配一个值。在哪里? number 是非空的, @ 被赋予 数 . 在哪里? 数 是空的 COALESCE() 默认为上一个值 @ . 无论哪种情况,这都成为 数 列,更新继续到下一行。这个 @ 变量在一行之间保留其值,因此后续行获取来自前一行的值。更新的顺序是可预测的,因为mysql特别使用order by和update(这不是标准的sql)。

最好的解决办法是比尔·卡温提出的。我最近不得不在一个相对较大的resultset(1000行,12列,每列需要这种类型的“如果当前行上的值为空,则显示最后一个非空值”)中解决这个问题,并使用update方法,对之前运行的已知值(或带有top 1的子查询)使用top 1 select超慢。

我使用的是sql 2005,变量替换的语法与mysql略有不同:

UPDATE mytable 
SET 
    @n = COALESCE(number, @n),
    number = COALESCE(number, @n)
ORDER BY date

如果“number”不为空,则第一个set语句将变量@n的值更新为当前行的“number”值(coalesce返回传递给它的第一个非空参数) 第二个set语句将“number”的实际列值更新为自身(如果不为空)或变量@n(始终包含遇到的最后一个非空值)。

这种方法的优点是不需要额外的资源来一遍又一遍地扫描临时表…@n的行内更新负责跟踪最后一个非空值。

我没有足够的代表投票支持他的回答,但应该有人支持。这是最优雅和最好的表演。

这是Oracle解决方案(10g或更高版本)。它使用解析函数 last_value() 与 ignore nulls 选项,它替换列的最后一个非空值。

SQL> select *
  2  from mytable
  3  order by id
  4  /

        ID    SOMECOL
---------- ----------
         1          3
         2
         3          5
         4
         5
         6          2

6 rows selected.

SQL> select id
  2         , last_value(somecol ignore nulls) over (order by id) somecol
  3  from mytable
  4  /

        ID    SOMECOL
---------- ----------
         1          3
         2          3
         3          5
         4          5
         5          5
         6          2

6 rows selected.

SQL>

下面的脚本解决了这个问题,只使用普通的ansi sql。我测试了这个溶液 SQL2008 , SQLite3 和 Oracle11g .

CREATE TABLE test(mysequence INT, mynumber INT);

INSERT INTO test VALUES(1, 3);
INSERT INTO test VALUES(2, NULL);
INSERT INTO test VALUES(3, 5);
INSERT INTO test VALUES(4, NULL);
INSERT INTO test VALUES(5, NULL);
INSERT INTO test VALUES(6, 2);

SELECT t1.mysequence, t1.mynumber AS ORIGINAL
, (
    SELECT t2.mynumber
    FROM test t2
    WHERE t2.mysequence = (
        SELECT MAX(t3.mysequence)
        FROM test t3
        WHERE t3.mysequence <= t1.mysequence
        AND mynumber IS NOT NULL
       )
) AS CALCULATED
FROM test t1;

我知道这是一个非常古老的论坛,但我在解决我的问题时遇到了这个问题:)刚刚意识到其他人对上述问题给出了一些复杂的解决方案。请看下面我的解决方案:

DECLARE @A TABLE(ID INT, Val INT)

INSERT INTO @A(ID,Val) SELECT 1, 3
INSERT INTO @A(ID,Val) SELECT 2, NULL
INSERT INTO @A(ID,Val) SELECT 3, 5
INSERT INTO @A(ID,Val) SELECT 4, NULL
INSERT INTO @A(ID,Val) SELECT 5, NULL
INSERT INTO @A(ID,Val) SELECT 6, 2

UPDATE D
    SET D.VAL = E.VAL
    FROM (SELECT A.ID C_ID, MAX(B.ID) P_ID
          FROM  @A AS A
           JOIN @A AS B ON A.ID > B.ID
          WHERE A.Val IS NULL
            AND B.Val IS NOT NULL
          GROUP BY A.ID) AS C
    JOIN @A AS D ON C.C_ID = D.ID
    JOIN @A AS E ON C.P_ID = E.ID

SELECT * FROM @A

希望这可以帮助某人:)

一般来说:

UPDATE MyTable
SET MyNullValue = MyDate
WHERE MyNullValue IS NULL

如果您正在寻找redshift的解决方案,这将适用于frame子句:

SELECT date, 
       last_value(columnName ignore nulls) 
                   over (order by date
                         rows between unbounded preceding and current row) as columnName 
 from tbl

首先,你真的需要存储这些值吗?您可以使用执行此操作的视图:

SELECT  t."date",
        x."number" AS "number"
FROM    @Table t
JOIN    @Table x
    ON  x."date" = (SELECT  TOP 1 z."date"
                    FROM    @Table z
                    WHERE   z."date" <= t."date"
                        AND z."number" IS NOT NULL
                    ORDER BY z."date" DESC)

如果你真的有 ID ("date") 列,它是主键(集群),那么这个查询应该非常快。但请检查查询计划:最好有一个包含 Val 也列。

如果你不喜欢程序,当你可以避免它们时,你也可以使用类似的查询。 UPDATE :

UPDATE  t
SET     t."number" = x."number"
FROM    @Table t
JOIN    @Table x
    ON  x."date" = (SELECT  TOP 1 z."date"
                    FROM    @Table z
                    WHERE   z."date" < t."date" --//@note: < and not <= here, as = not required
                        AND z."number" IS NOT NULL
                    ORDER BY z."date" DESC)
WHERE   t."number" IS NULL

注意:代码必须在“sql server”上运行。

这是MS访问的解决方案。

示例表被调用 tab ,带字段 id 和 val .

SELECT (SELECT last(val)
          FROM tab AS temp
          WHERE tab.id >= temp.id AND temp.val IS NOT NULL) AS val2, *
  FROM tab;

UPDATE TABLE
   SET number = (SELECT MAX(t.number)
                  FROM TABLE t
                 WHERE t.number IS NOT NULL
                   AND t.date < date)
 WHERE number IS NULL

试试这个:

update Projects
set KickOffStatus=2 
where KickOffStatus is null