blocks|key|1251888|text|在非常一般的意义上：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1251889|UPDATE+MyTable
SET+MyNullValue+=+MyDate
WHERE+MyNullValue+IS+NULL|code-block|syntax|javascript|1251890|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In a very general sense:

<pre><code>UPDATE MyTable
SET MyNullValue = MyDate
WHERE MyNullValue IS NULL
</code></pre>

blocks|key|1251913|text|如果您使用的是Sql+Server，则应该可以执行此操作|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1251914|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|code-block|syntax|javascript|1251915|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you are using Sql Server this should work

<pre><code>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 &lt; t.ID AND Val IS NOT NULL ORDER BY ID DESC))
FROM @Table t
</code></pre>

blocks|key|1251919|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1251920|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>UPDATE TABLE
 SET number = (SELECT MAX(t.number)
 FROM TABLE t
 WHERE t.number IS NOT NULL
 AND t.date &lt; date)
 WHERE number IS NULL
</code></pre>

blocks|key|1251967|text|以下是Oracle解决方案(10g或更高版本)。它使用带有ignore+nulls选项的分析函数last_value()，用最后一个非NULL值替换该列。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1251968|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>|code-block|syntax|javascript|1251969|entityMap^0|T|C|1C|C|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

Here is the Oracle solution (10g or higher). It uses the analytic function <code>last_value()</code> with the <code>ignore nulls</code> option, which substitutes the last non-null value for the column.

<pre><code>SQL&gt; select *
 2 from mytable
 3 order by id
 4 /

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

6 rows selected.

SQL&gt; 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&gt;
</code></pre>

blocks|key|1252058|text|最好的解决方案是Bill+Karwin提供的解决方案。我最近不得不在一个相对较大的结果集中解决这个问题(1000行，每行有12列，每行都需要这种类型的“如果此值在当前行上为null，则显示最后一个非空值”)，并且使用update方法对上一个已知值使用top+1+select+(或者说带有top+1的子查询)运行速度非常慢。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1252059|我使用的是SQL+2005，变量替换的语法与mysql略有不同：|1252060|UPDATE+mytable+
SET+
++++@n+=+COALESCE(number,+@n),
++++number+=+COALESCE(number,+@n)
ORDER+BY+date|code-block|syntax|javascript|1252061|第一个set语句将变量@n的值更新为当前行的值'number‘，如果'number’不为null+(COALESCE返回传入的第一个非NULL参数)。第二个set语句将'number‘的实际列值更新为自身(如果不为null)或变量@n+(始终包含遇到的最后一个非NULL值)。|1252062|这种方法的好处是不需要花费额外的资源一遍又一遍地扫描临时表……@n的行内更新负责跟踪最后一个非空值。|1252063|我没有足够的代表投票支持他的答案，但应该有人支持。这是最优雅和最好的表演。|1252064|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

The best solution is the one offered by Bill Karwin. I recently had to solve this in a relatively large resultset (1000 rows with 12 columns each needing this type of "show me last non-null value if this value is null on the current row") and using the update method with a top 1 select for the previous known value (or subquery with a top 1 ) ran super slow.

I am using SQL 2005 and the syntax for a variable replacement is slightly different than mysql:

<pre><code>UPDATE mytable 
SET 
 @n = COALESCE(number, @n),
 number = COALESCE(number, @n)
ORDER BY date
</code></pre>

The first set statement updates the value of the variable @n to the current row's value of 'number' if the 'number' is not null (COALESCE returns the first non-null argument you pass into it)
The second set statement updates the actual column value for 'number' to itself (if not null) or the variable @n (which always contains the last non NULL value encountered).

The beauty of this approach is that there are no additional resources expended on scanning the temporary table over and over again... The in-row update of @n takes care of tracking the last non-null value.

I don't have enough rep to vote his answer up, but someone should. It's the most elegant and best performant.

blocks|key|1252100|text|我知道这是一个非常古老的论坛，但我在解决我的问题时遇到了这个问题:)我刚刚意识到其他人对上面的问题给出了有点复杂的解决方案。下面是我的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1252101|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|code-block|syntax|javascript|1252102|希望这对某些人有帮助：)|1252103|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I know it is a very old forum, but I came across this while troubleshooting my problem :) just realised that the other guys have given bit complex solution to the above problem. Please see my solution below:

<pre><code>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 &gt; 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
</code></pre>

Hope this may help someone:)

blocks|key|1252134|text|这是MS+Access的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1252135|示例表名为tab，具有字段id和val。|offset|length|style|CODE|1252136|SELECT+(SELECT+last(val)
++++++++++FROM+tab+AS+temp
++++++++++WHERE+tab.id+>=+temp.id+AND+temp.val+IS+NOT+NULL)+AS+val2,+*
++FROM+tab;|code-block|syntax|javascript|1252137|entityMap^0|0|5|3|D|2|G|3|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]|$D|S|E|T|F|G]|$D|U|E|V|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|W|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|N|$]]

This is the solution for MS Access.

The example table is called <code>tab</code>, with fields <code>id</code> and <code>val</code>.

<pre><code>SELECT (SELECT last(val)
 FROM tab AS temp
 WHERE tab.id &gt;= temp.id AND temp.val IS NOT NULL) AS val2, *
 FROM tab;
</code></pre>

blocks|key|1252176|text|下面的脚本解决了这个问题，并且只使用普通的ANSI+SQL。我在SQL2008、SQLite3和Oracle11g上测试了这个解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1252177|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;|code-block|syntax|javascript|1252178|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!3/13c01/1|1|http://sqlfiddle.com/#!7/13c01/1|2|http://sqlfiddle.com/#!4/13c01/8^0|W|7|0|14|7|1|1C|9|2|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]|$A|Y|B|Z|1|10]|$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|F|7|14|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|15|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]|Q|$5|L|M|N|C|$O|R]]|S|$5|L|M|N|C|$O|T]]]]

The following script solves this problem and only uses plain ANSI SQL. I tested this solution on <a href="http://sqlfiddle.com/#!3/13c01/1" rel="noreferrer">SQL2008</a>, <a href="http://sqlfiddle.com/#!7/13c01/1" rel="noreferrer">SQLite3</a> and <a href="http://sqlfiddle.com/#!4/13c01/8" rel="noreferrer">Oracle11g</a>.

<pre><code>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 &lt;= t1.mysequence
 AND mynumber IS NOT NULL
 )
) AS CALCULATED
FROM test t1;
</code></pre>

blocks|key|1252229|text|如果你正在寻找Redshift的解决方案，这将与frame子句一起工作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1252230|SELECT+date,+
+++++++last_value(columnName+ignore+nulls)+
+++++++++++++++++++over+(order+by+date
+++++++++++++++++++++++++rows+between+unbounded+preceding+and+current+row)+as+columnName+
+from+tbl|code-block|syntax|javascript|1252231|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you're looking for a solution for Redshift, this will work with the frame clause:

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

blocks|key|1467019|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1467020|update+Projects
set+KickOffStatus=2+
where+KickOffStatus+is+null|code-block|syntax|javascript|1467021|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>update Projects
set KickOffStatus=2 
where KickOffStatus is null
</code></pre>

blocks|key|1467060|text|如果您有一个标识(Id)和一个公共(Type)列：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1467061|UPDATE+#Table1+
SET+[Type]+=+(SELECT+TOP+1+[Type]
++++++++++++++FROM+#Table1+t++++++++++++++
++++++++++++++WHERE+t.[Type]+IS+NOT+NULL+AND+
++++++++++++++b.[Id]+>+t.[Id]
++++++++++++++ORDER+BY+t.[Id]+DESC)
FROM+#Table1+b
WHERE+b.[Type]+IS+NULL|code-block|syntax|javascript|1467062|entityMap^0|9|2|I|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

In case you have one identity (<code>Id</code>) and one common (<code>Type</code>) columns:

<pre><code>UPDATE #Table1 
SET [Type] = (SELECT TOP 1 [Type]
 FROM #Table1 t 
 WHERE t.[Type] IS NOT NULL AND 
 b.[Id] &gt; t.[Id]
 ORDER BY t.[Id] DESC)
FROM #Table1 b
WHERE b.[Type] IS NULL
</code></pre>

blocks|key|1467067|text|这将在Snowflake上起作用(归功于Darren+Gardner)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1467068|create+temp+table+ss+(id+int,+val+int);
insert+into+ss+(id,val)+select+1,+3;
insert+into+ss+(id,val)+select+2,+null;
insert+into+ss+(id,val)+select+3,+5;
insert+into+ss+(id,val)+select+4,+null;
insert+into+ss+(id,val)+select+5,+null;
insert+into+ss+(id,val)+select+6,+2;

select+*
++++++,last_value(val+ignore+nulls)+over+
+++++++(order+by+id+rows+between+unbounded+preceding+and+current+row)+as+val2
++from+ss;|code-block|syntax|javascript|1467069|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This will work on Snowflake (credit to Darren Gardner):
<pre><code>create temp table ss (id int, val int);
insert into ss (id,val) select 1, 3;
insert into ss (id,val) select 2, null;
insert into ss (id,val) select 3, 5;
insert into ss (id,val) select 4, null;
insert into ss (id,val) select 5, null;
insert into ss (id,val) select 6, 2;

select *
 ,last_value(val ignore nulls) over 
 (order by id rows between unbounded preceding and current row) as val2
 from ss;
</code></pre>

I have 2 columns

<pre><code>date number 
---- ------
1 3 
2 NULL 
3 5 
4 NULL 
5 NULL 
6 2 
.......
</code></pre>

I need to replace the NULL values with new values takes on the value from the last known value in the previous date in the date column
eg: date=2 number = 3, date 4 and 5 number = 5 and 5. The NULL values appear randomly.

SQL QUERY replace NULL value in a row with a value from the previous known value

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我有两列date   number       ----   ------1      3           2      NULL        3      5           4      NULL        5      NULL        6      2          .......我需要...

问SQL查询将行中的空值替换为先前已知值中的值
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL查询将行中的空值替换为先前已知值中的值EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL查询将行中的空值替换为先前已知值中的值
EN