blocks|key|307531|text|当您想用其他东西替换可能的null列时，可以使用IsNull。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|307532|SELECT+ISNULL(myColumn,+0+)+FROM+myTable|code-block|syntax|javascript|307533|这将把一个0放在myColumn中，如果它一开始就是空的。|307534|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms184325.aspx^0|D|4|O|6|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|11|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

When you want to replace a possibly <code>null</code> column with something else, use <a href="http://msdn.microsoft.com/en-us/library/ms184325.aspx" rel="noreferrer">IsNull</a>.

<pre><code>SELECT ISNULL(myColumn, 0 ) FROM myTable
</code></pre>

This will put a 0 in myColumn if it is null in the first place.

blocks|key|307494|text|使用coalesce|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|307495|coalesce(column_name,0)|code-block|syntax|javascript|307496|不过，在对when+condition+then+1求和时，您可以同样轻松地将sum更改为count+-例如：|307497|count(case+when+c.runstatus+=+'Succeeded'+then+1+end)+as+Succeeded,|307498|(Count(null)返回0，而sum(null)返回null。)|307499|entityMap^0|2|8|0|0|5|L|13|3|19|5|0|0|1|B|H|9|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|14|8|@$9|15|A|16|B|C]|$9|17|A|18|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|R|$]]

With <code>coalesce</code>:

<pre><code>coalesce(column_name,0)
</code></pre>

Although, where summing <code>when condition then 1</code>, you could just as easily change <code>sum</code> to <code>count</code> - eg:

<pre><code>count(case when c.runstatus = 'Succeeded' then 1 end) as Succeeded,
</code></pre>

(<code>Count(null)</code> returns 0, while <code>sum(null)</code> returns null.)

blocks|key|307711|text|如果您使用的是Presto、AWS+Athena等，则没有ISNULL()函数。取而代之的是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|307712|SELECT+COALESCE(myColumn,+0+)+FROM+myTable|code-block|syntax|javascript|307713|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you are using Presto, AWS Athena etc, there is no ISNULL() function. Instead, use:

<pre><code>SELECT COALESCE(myColumn, 0 ) FROM myTable
</code></pre>

blocks|key|90506|text|在下面的代码中包装您的列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|90507|+ISNULL(Yourcolumn,+0)|code-block|syntax|javascript|90508|也许可以检查一下为什么会得到空值|90509|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Wrap your column in this code.

<pre><code> ISNULL(Yourcolumn, 0)
</code></pre>

Maybe check why you are getting nulls

blocks|key|307563|text|使用COALESCE，它返回第一个非空值，例如|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|307564|SELECT+COALESCE(sum(case+when+c.runstatus+=+'Succeeded'+then+1+end),+0)+as+Succeeded|code-block|syntax|javascript|307565|如果返回为NULL，则将Succeeded设置为0。|307566|entityMap^0|2|8|0|0|5|4|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

Use <code>COALESCE</code>, which returns the first not-null value e.g.

<pre><code>SELECT COALESCE(sum(case when c.runstatus = 'Succeeded' then 1 end), 0) as Succeeded
</code></pre>

Will set Succeeded as 0 if it is returned as <code>NULL</code>.

blocks|key|307632|text|将else添加到case语句中，以便在未找到测试条件时将它们默认为零。此时，如果没有找到测试条件，则会将NULL传递给SUM()函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|307633|++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)%2B'/'%2Bsubstring(convert(varchar(8),run_date),5,2)%2B'/'++++++++++%2Bsubstring(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|code-block|syntax|javascript|307634|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Add an else to your case statements so that they default to zero if the test condition is not found. At the moment if the test condition isn't found NULL is being passed to the SUM() function.

<pre><code> 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
</code></pre>

blocks|key|307662|text|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,+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|307663|这里的问题是，如果没有else语句，当运行状态不是列描述中声明的状态时，您必然会收到一个Null。向Null添加任何内容都会导致Null，这就是此查询的问题所在。|unstyled|307664|祝好运!|307665|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>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, 
</code></pre>

the issue here is that without the else statement, you are bound to receive a Null when the run status isn't the stated status in the column description. Adding anything to Null will result in Null, and that is the issue with this query.

Good Luck!

blocks|key|90594|text|按照前面的回答，我丢失了SQL+server+db中的列名，但是遵循此语法还可以帮助我保留ColumnName。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|90595|ISNULL(MyColumnName,+0)+MyColumnName|code-block|syntax|javascript|90596|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

by following previous answers I was losing my column name in SQL server db however following this syntax helped me to retain the ColumnName as well 

<pre><code>ISNULL(MyColumnName, 0) MyColumnName
</code></pre>

blocks|key|90654|text|对于常规的SQL，ISNULL(item)只能接受一个参数，因此90%25的解决方案都不起作用。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|90655|我将@Krishna+Chavali的答案重新定义为：|90656|(CASE+WHEN+(NOT+ISNULL(column_name))+THEN+column_name+ELSE+0+END)+AS+ColumnName|code-block|syntax|javascript|90657|如果不是null，则返回column_name中的值；如果为null，则返回0。|90658|entityMap^0|9|C|0|0|0|4|4|C|B|U|4|12|1|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|U|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|V|8|@$9|W|A|X|B|C]|$9|Y|A|Z|B|C]|$9|10|A|11|B|C]|$9|12|A|13|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|P|$]]

For regular SQL, <code>ISNULL(item)</code> can only take one parameter, and thus 90% of these solutions don't work.
I repurposed @Krishna Chavali's answer to make this:
<pre><code>(CASE WHEN (NOT ISNULL(column_name)) THEN column_name ELSE 0 END) AS ColumnName
</code></pre>
This will return the value in <code>column_name</code> if it is not <code>null</code>, and <code>0</code> if it is <code>null</code>.

blocks|key|90634|text|UPDATE+TableName+SET+ColumnName=+ISNULL(ColumnName,+0+)+WHERE+Id+=+10|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|90635|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>UPDATE TableName SET ColumnName= ISNULL(ColumnName, 0 ) WHERE Id = 10
</code></pre>

I have developed a query, and in the results for the first three columns I get <code>NULL</code>. How can I replace it with <code>0</code>? 

<pre><code> 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
</code></pre>

Replacing NULL with 0 in a SQL server query

我已经开发了一个查询，在前三列的结果中，我得到了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(

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

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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