blocks|key|1232259|text|sum()函数自动忽略NULL。要做您想做的事情，您需要一个显式的checK：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1232260|select+(case+when+count(foo)+=+count(*)+then+sum(foo)+end)
from+#tmp;|code-block|syntax|javascript|1232261|如果要显式，可以将else+NULL添加到case语句中。|1232262|这背后的逻辑是count(foo)在foo中计数非空值的数量。如果这等于所有行，那么所有的值都是非空的。您可以使用更详细的：|1232263|select+(case+when+sum(case+when+foo+is+null+then+1+else+0+end)+>+0
+++++++++++++then+sum(foo)
++++++++end)|1232264|而且，我想指出，这个标题是相当误导人的。1+%2B+NULL+=+NULL。问题在于聚合函数，而不是算术运算符。|1232265|entityMap^0|0|5|B|4|0|0|9|9|L|4|0|7|A|I|3|0|0|K|F|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|15|8|@$9|16|A|17|B|C]|$9|18|A|19|B|C]]|D|@]|E|$]]|$1|O|3|P|5|H|7|1A|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|1B|8|@$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1E|8|@]|D|@]|E|$]]]|T|$]]

The <code>sum()</code> function automatically ignores <code>NULL</code>. To do what you want, you need an explicit checK:

<pre><code>select (case when count(foo) = count(*) then sum(foo) end)
from #tmp;
</code></pre>

If you want to be explicit, you could add <code>else NULL</code> to the <code>case</code> statement.

The logic behind this is that <code>count(foo)</code> counts the number of non-NULL values in <code>foo</code>. If this is equal to all the rows, then all the values are non-NULL. You could use the more verbose:

<pre><code>select (case when sum(case when foo is null then 1 else 0 end) &gt; 0
 then sum(foo)
 end)
</code></pre>

And, I want to point out that the title is quite misleading. <code>1 + NULL = NULL</code>. The issue is with the aggregation functions, not the arithmetic operators.

blocks|key|1775328|text|使用EXISTS查找空值可能是最快的：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1775329|SELECT
++++CASE+WHEN+EXISTS(SELECT+NULL+FROM+tmp+WHERE+foo+IS+NULL)
++++++++THEN+NULL
++++++++ELSE+(SELECT+sum(foo)+from+tmp)
++++END|code-block|syntax|javascript|1775330|entityMap^0|2|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Looking for a null value with <code>EXISTS</code> may be the fastest:

<pre><code>SELECT
 CASE WHEN EXISTS(SELECT NULL FROM tmp WHERE foo IS NULL)
 THEN NULL
 ELSE (SELECT sum(foo) from tmp)
 END
</code></pre>

blocks|key|1775345|text|只管说|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1775346|select+case+sign(sum(case+when+foo+is+null+then+1+else+0+end))
+++++++when+1+then+null
+++++++else++++++++sum(foo)
+++++++end
+from+some_table
+...
+group+by
+...|code-block|syntax|javascript|1775347|这就是你所需要的。|1775348|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|$]]

Just say

<pre><code>select case sign(sum(case when foo is null then 1 else 0 end))
 when 1 then null
 else sum(foo)
 end
 from some_table
 ...
 group by
 ...
</code></pre>

That's about all you need.

Every question I search for about the warning 

<blockquote>
 Warning: Null value is eliminated by an aggregate or other SET operation.
</blockquote>

Typically people want to treat the NULL values as 0. I want the opposite, how do I modify the following stored procedure to make it return NULL instead of 1?

<pre><code>CREATE PROCEDURE TestProcedure 
AS
BEGIN
select cast(null as int) as foo into #tmp

insert into #tmp values (1)

select sum(foo) from #tmp
END
GO
</code></pre>

I thought it would be <code>SET ANSI_NULLS ON</code> (I tried before the declaration, within the procedure itself, and before executing the procedure in my test query) but that did not appear to change the behavior of <code>SUM(</code>.

Change aggregate functions to output NULL when a element is NULL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我搜索到的每一个关于警告的问题警告: Null值由聚合操作或其他设置操作消除。通常，人们希望将空值视为0。相反，如何修改以下存储过程，使其返回NULL而不是1？CREATE PROCEDURE TestProcedure ASBEGINselect cast(null as int) as foo into #tmpi...

问当元素为NULL时，将聚合函数更改为输出NULL
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问当元素为NULL时，将聚合函数更改为输出NULLEN