Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- date number
- ---- ------
- 1 3
- 2 NULL
- 3 5
- 4 NULL
- 5 NULL
- 6 2
- .......
- 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
- UPDATE mytable
- SET number = (@n := COALESCE(number, @n))
- ORDER BY date;
- 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>
- UPDATE mytable
- SET
- @n = COALESCE(number, @n),
- number = COALESCE(number, @n)
- ORDER BY date
- 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
- 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;
- UPDATE MyTable
- SET MyNullValue = MyDate
- WHERE MyNullValue IS NULL
- 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)
- 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
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement