在SQL server查询中将NULL替换为0

我已经开发了一个查询，在前三列的结果中，我得到NULL。我怎么把它换成0呢?

Select c.rundate, 
  sum(case when c.runstatus = 'Succeeded' then 1 end) as Succeeded, 
  sum(case when c.runstatus = 'Failed' then 1 end) as Failed, 
  sum(case when c.runstatus = 'Cancelled' then 1 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。)

2013-05-30 15:49:03

其他回答

对于常规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。

2021-12-29 05:56:04

你说的前三列，是指求和列吗?如果是，将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小提琴演示

2013-05-30 15:49:44

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

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

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

2013-05-30 15:49:50

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

ISNULL(Yourcolumn, 0)

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

2013-05-30 15:50:14

合并:

coalesce(column_name,0)

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

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

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

2013-05-30 15:49:03

在SQL server查询中将NULL替换为0

推荐文章

最新文章

标签