SQL QUERY заменяет значение NULL в строке со значением из предыдущего известного значения

Ответ 1

Если вы используете 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

Ответ 2

Здесь решение MySQL:

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

Это красноречиво, но не обязательно работать в других брендах РСУБД. Для других брендов может быть особое решение для конкретной марки. Поэтому важно сказать нам, какой бренд вы используете.

Приятно быть независимым от вендора, как прокомментировал @Pax, но в противном случае неплохо использовать свой выбранный бренд базы данных в полной мере.

Объяснение приведенного выше запроса:

@n - переменная пользователя MySQL. Он начинается с NULL и присваивается значение для каждой строки, когда UPDATE проходит через строки. Если number не является NULL, @n присваивается значение number. Где number равно NULL, значение COALESCE() по умолчанию соответствует предыдущему значению @n. В любом случае это становится новым значением столбца number, и UPDATE переходит к следующей строке. Переменная @n сохраняет свое значение от строки к строке, поэтому последующие строки получают значения, которые поступают из предыдущих строк (строк). Порядок UPDATE предсказуем из-за использования MySQL специального ORDER BY с UPDATE (это не стандартный SQL).

Ответ 3

Лучшее решение - это предложение, предложенное Биллом Карвином. Мне недавно пришлось решить эту задачу в относительно большом наборе результатов (1000 строк с 12 столбцами, каждый из которых нуждается в этом типе "показать мне последнее ненулевое значение, если это значение равно нулю в текущей строке" ) и использовать метод обновления с верхним 1 выберите предыдущее известное значение (или подзапрос с верхним 1) работает супер медленно.

Я использую SQL 2005, и синтаксис замены переменных немного отличается от mysql:

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

Первый оператор набора обновляет значение переменной @n до значения текущей строки "number", если "number" не является нулевым (COALESCE возвращает первый непустой аргумент, который вы передаете в него) Второй оператор набора обновляет фактическое значение столбца для 'number' для себя (если не null) или переменную @n (которая всегда содержит последнее значение, отличное от NULL).

Красота такого подхода заключается в том, что дополнительные ресурсы, затрачиваемые на сканирование временной таблицы, снова и снова не используются... Обновление строки @n в строках отслеживает последнее ненулевое значение.

У меня недостаточно голосов, чтобы проголосовать за его ответ, но кто-то должен. Это самый элегантный и лучший исполнитель.

Ответ 4

Вот решение Oracle (10g или выше).

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>

Ответ 5

Следующий script решает эту проблему и использует только обычный 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;

Ответ 6

Я знаю, что это очень старый форум, но я столкнулся с этим, когда искал свою проблему:) просто понял, что другие ребята дали сложное решение этой проблемы. См. Мое решение ниже:

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

Надеюсь, это может помочь кому-то:)

Ответ 7

В очень общем смысле:

UPDATE MyTable
SET MyNullValue = MyDate
WHERE MyNullValue IS NULL

Ответ 8

Прежде всего, вам действительно нужно хранить значения? Вы можете просто использовать представление, которое выполняет задание:

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".

Ответ 9

Это решение для MS Access.

Примерная таблица называется 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;

Ответ 10

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

Ответ 11

Если вы ищете решение для Redshift, это будет работать с предложением frame:

SELECT date, last_value (columnName ignore nulls) over (упорядочить по дате строки между неограниченной предыдущей и текущей строкой) как columnName из TBL

Ответ 12

Попробуйте следующее:

update Projects
set KickOffStatus=2 
where KickOffStatus is null