如何用特定值替换空值以及如何增加数字 [英] How to Replace null values with specific value and how to increment the numbers

问题描述

我有一个如下所述的表格.

Hi,

I have a table like as i mentioned below.

COL A
10
NULL
20
NULL
40
20
NULL

我想像下面提到的那样输出，请您帮我一下.

i wanted to output like as i mentioned below can you please help me on this.

Col A
20
10
30
10
50
30
10

推荐答案

ISNULL()在这方面可以为您提供帮助.
试试这个

ISNULL() can help you in this regard.
Try this

SELECT A,ISNULL(A,0)+10 AS B
FROM YourTableName

示例:

Example:

SELECT A,ISNULL(A,0)+10 AS B
FROM 
(
	SELECT 10 AS A
	UNION ALL
	SELECT NULL
	UNION ALL
	SELECT 20
	UNION ALL
	SELECT NULL
	UNION ALL
	SELECT 40
	UNION ALL
	SELECT 20
	UNION ALL
	SELECT NULL
) AS T

输出:

Output:

A	    B
------------------
10	     20
NULL	 10
20	     30
NULL	 10
40	     50
20	     30
NULL	 10

希望对您有帮助:)

Hope, it helps :)

另一种方法是使用

Another way is to use COALESCE function[^]

SELECT COALESCE(ColA, 0) + 10 AS ColB
FROM TableName

如果要更新表(替换现有值)，请使用以下命令:

In case you want to update your table (replace existing values), use this:

UPDATE t1 SET t1.ColA = COALESCE(t2.ColA, 0) + 10
FROM TableName AS t1 INNER JOIN TableName AS t2 ON t1.PK = t2.PK

其中PK表示:主键

where PK means: Primary Key

这篇关于如何用特定值替换空值以及如何增加数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！