刚毅的草稿纸 · 数据透视表|数据透视表的联动· 4 月前 · |
玩足球的斑马 · user interface - ...· 10 月前 · |
睿智的小熊猫 · 快速删除空行及Java注释_架构师的小跟班的 ...· 1 年前 · |
乐观的机器猫 · 用户对问题“使用dockerfile生成一个 ...· 1 年前 · |
迷茫的红豆 · 神经网络训练时对输入有什么特别的要求吗? - 知乎· 1 年前 · |
我已经开发了一个查询,在前三列的结果中,我得到了
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
coalesce(column_name,0)
不过,在对
when condition then 1
求和时,您可以同样轻松地将
sum
更改为
count
-例如:
count(case when c.runstatus = 'Succeeded' then 1 end) as Succeeded,
(
Count(null)
返回0,而
sum(null)
返回null。)
当您想用其他东西替换可能的
null
列时,可以使用
IsNull
。
SELECT ISNULL(myColumn, 0 ) FROM myTable
这将把一个0放在myColumn中,如果它一开始就是空的。
使用
COALESCE
,它返回第一个非空值,例如
SELECT COALESCE(sum(case when c.runstatus = 'Succeeded' then 1 end), 0) as Succeeded
如果返回为
NULL
,则将Succeeded设置为0。
在下面的代码中包装您的列。
ISNULL(Yourcolumn, 0)
也许可以检查一下为什么会得到空值
将else添加到case语句中,以便在未找到测试条件时将它们默认为零。此时,如果没有找到测试条件,则会将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