合并:

coalesce(column_name,0)

虽然，当条件为1时求和，你可以很容易地将sum更改为count -例如:

count(case when c.runstatus = 'Succeeded' then 1 end) as Succeeded,

(Count(null)返回0，而sum(null)返回null。)

当你想用其他东西替换一个可能为空的列时，使用IsNull。

SELECT ISNULL(myColumn, 0 ) FROM myTable

这将在myColumn中放置一个0，如果它首先是空的。

你说的前三列，是指求和列吗?如果是，将ELSE 0添加到CASE语句中。NULL值的SUM为NULL。

sum(case when c.runstatus = 'Succeeded' then 1 else 0 end) as Succeeded, 
sum(case when c.runstatus = 'Failed' then 1 else 0 end) as Failed, 
sum(case when c.runstatus = 'Cancelled' then 1 else 0 end) as Cancelled, 

SQL小提琴演示

使用COALESCE，它返回第一个非空值。

SELECT COALESCE(sum(case when c.runstatus = 'Succeeded' then 1 end), 0) as Succeeded

如果返回NULL，则将Succeeded设置为0。

在这段代码中包装您的列。

ISNULL(Yourcolumn, 0)

也许检查一下为什么得到null

在case语句中添加else，这样如果没有找到测试条件，它们默认为0。此时，如果没有找到测试条件，则将NULL传递给SUM()函数。

Select c.rundate, 
  sum(case when c.runstatus = 'Succeeded' then 1 else 0 end) as Succeeded, 
  sum(case when c.runstatus = 'Failed' then 1 else 0 end) as Failed, 
  sum(case when c.runstatus = 'Cancelled' then 1 else 0 end) as Cancelled, 
  count(*) as Totalrun from
  (    Select a.name,case when b.run_status=0 Then 'Failed' when b.run_status=1 Then 'Succeeded'
  when b.run_status=2 Then 'Retry' Else 'Cancelled' End as Runstatus,
  ---cast(run_date as datetime)
              cast(substring(convert(varchar(8),run_date),1,4)+'/'+substring(convert(varchar(8),run_date),5,2)+'/'          +substring(convert(varchar(8),run_date),7,2) as Datetime) as RunDate
  from msdb.dbo.sysjobs as a(nolock) inner join msdb.dbo.sysjobhistory as b(nolock) 
  on a.job_id=b.job_id
  where a.name='AI'
  and b.step_id=0) as c
  group by 
  c.rundate

你可以使用这两种方法，但有区别:

SELECT ISNULL(col1, 0 ) FROM table1
SELECT COALESCE(col1, 0 ) FROM table1

对比COALESCE()和ISNULL():

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently. Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times. Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence. The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example.

—此语句失败，因为PRIMARY KEY不能接受NULL值 ——以及col2的COALESCE表达式的可空性 ——求值为NULL。

CREATE TABLE #Demo 
( 
    col1 integer NULL, 
    col2 AS COALESCE(col1, 0) PRIMARY KEY, 
    col3 AS ISNULL(col1, 0) 
); 

对象的可空性使该语句成功 ——ISNULL函数的值为NOT NULL。

CREATE TABLE #Demo 
( 
    col1 integer NULL, 
    col2 AS COALESCE(col1, 0), 
    col3 AS ISNULL(col1, 0) PRIMARY KEY 
);

ISNULL和COALESCE的验证也是不同的。例如, ISNULL的NULL值转换为int，而COALESCE的NULL值转换为int， 您必须提供一个数据类型。 ISNULL只接受2个参数，而COALESCE接受一个变量 参数个数。 如果你需要了解更多，这里是来自msdn的完整文档。

一个简单的方法是

UPDATE tbl_name SET fild_name = value WHERE fild_name IS NULL

sum(case when c.runstatus = 'Succeeded' then 1 else 0 end) as Succeeded, 
sum(case when c.runstatus = 'Failed' then 1 else 0 end) as Failed, 
sum(case when c.runstatus = 'Cancelled' then 1 else 0 end) as Cancelled, 

这里的问题是，如果没有else语句，当运行状态不是列描述中声明的状态时，您一定会收到一个Null。将任何东西添加到Null都会导致Null，这就是这个查询的问题。

好运！

通过遵循之前的答案，我在SQL server db中丢失了我的列名，但是遵循这种语法帮助我保留了ColumnName

ISNULL(MyColumnName, 0) MyColumnName

如果你正在使用Presto, AWS Athena等，没有ISNULL()函数。相反,使用:

SELECT COALESCE(myColumn, 0 ) FROM myTable

UPDATE TableName SET ColumnName= ISNULL(ColumnName, 0 ) WHERE Id = 10

对于常规SQL, ISNULL(item)只能接受一个参数，因此90%的解决方案都不起作用。

我重新利用@Krishna Chavali的回答来说明:

(CASE WHEN (NOT ISNULL(column_name)) THEN column_name ELSE 0 END) AS ColumnName

如果column_name不为空，则返回该值;如果column_name为空，则返回0。