在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

合并:

coalesce(column_name,0)

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

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

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

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

SELECT COALESCE(myColumn, 0 ) FROM myTable

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

SELECT ISNULL(myColumn, 0 ) FROM myTable

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

在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

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

ISNULL(MyColumnName, 0) MyColumnName