blocks|key|2005005|text|在您的情况下，min()应该作为d的最低值返回什么？|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2005006|该错误通知您，min()函数没有考虑到null记录。|2005007|因此，如果它应该忽略NULL值并返回最低的现有日期，那么您可以忽略此警告。|BOLD|2005008|如果您还喜欢为这条语句取消警告，那么您可以这样做|2005009|set+ansi_warnings+off
select+@mindate+=+min(d)+from+t
set+ansi_warnings+on|code-block|syntax|javascript|2005010|如果您希望通过使用默认值来考虑NULL值，那么您可以设置一个默认值，如下所示|2005011|select+@mindate+=+min(isnull(d,+cast(0+as+datetime)))
from+t|2005012|entityMap^0|7|5|G|1|0|7|5|J|4|0|A|4|L|2|0|0|0|F|4|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@$9|Y|A|Z|B|C]|$9|10|A|11|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|17|8|@$9|18|A|19|B|C]|$9|1A|A|1B|B|J]]|D|@]|E|$]]|$1|K|3|L|5|6|7|1C|8|@]|D|@]|E|$]]|$1|M|3|N|5|O|7|1D|8|@]|D|@]|E|$P|Q]]|$1|R|3|S|5|6|7|1E|8|@$9|1F|A|1G|B|C]]|D|@]|E|$]]|$1|T|3|U|5|O|7|1H|8|@]|D|@]|E|$P|Q]]|$1|V|3|-4|5|6|7|1I|8|@]|D|@]|E|$]]]|W|$]]

What should <code>min()</code> return in your case as lowest value of <code>d</code>?

The error informs you that the <code>min()</code> function did not take records into account that are <code>null</code>.

So if it should ignore the <code>NULL</code> values and return the lowest existing date then you can ignore this warning. 

If you also like to suppress warnings for this single statement then you can do it like this

<pre><code>set ansi_warnings off
select @mindate = min(d) from t
set ansi_warnings on
</code></pre>

If you want <code>NULL</code> values taken into account by using a default value for them then you can set a default date value like this

<pre><code>select @mindate = min(isnull(d, cast(0 as datetime)))
from t
</code></pre>

blocks|key|40152|text|大多数情况下你不应该做任何事。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|40153|可以通过设置ansi_warnings关闭警告，但这有其他影响，例如，如何处理零除法，当查询使用索引视图、计算列或XML方法等功能时，可能会导致失败。|unordered-list-item|offset|length|style|CODE|40154|在某些有限的情况下，您可以重写聚合以避免它。例如，可以将COUNT(nullable_column)重写为SUM(CASE+WHEN+nullable_column+IS+NULL+THEN+0+ELSE+1+END)，但这并不总是能够在不更改语义的情况下直接完成。|40155|它只是一条信息消息SQL标准中要求的。除了在消息流中添加不必要的噪声之外，它没有任何不良影响(除了Server不能绕过读取NULL行之外，后者可以具有头顶，但禁用警告在这方面不能给出更好的执行计划)|40156|返回此消息的原因是在SQL中的大多数操作中传播。|40157|SELECT+NULL+%2B+3+%2B+7返回NULL+(将NULL视为未知数量，这是有意义的，因为?+%2B+3+%2B+7也是未知的)|40158|但|40159|SELECT+SUM(N)
FROM+++(VALUES+(NULL),
+++++++++++++++(3),
+++++++++++++++(7))+V(N)+|code-block|syntax|javascript|40160|返回10和忽略空值的警告。|40161|但是，这些都是，这正是典型聚合查询所需的语义。否则，单个NULL的存在将意味着该列对所有行的聚合最终都会产生NULL，这不是很有用。|BOLD|40162|是下面最重的蛋糕？+(像源，创意共享图像被我修改(裁剪和注释))|40163|​|40164|📷|atomic|40165|40166|在第三个蛋糕称好之后，天平断了，所以没有关于第四个蛋糕的信息，但是仍然有可能测量圆周。|40167|%2B--------%2B--------%2B---------------%2B
%7C+CakeId+%7C+Weight+%7C+Circumference+%7C
%2B--------%2B--------%2B---------------%2B
%7C++++++1+%7C+50+++++%7C+12.0++++++++++%7C
%7C++++++2+%7C+80+++++%7C+14.2++++++++++%7C
%7C++++++3+%7C+70+++++%7C+13.7++++++++++%7C
%7C++++++4+%7C+NULL+++%7C+13.4++++++++++%7C
%2B--------%2B--------%2B---------------%2B|40168|查询|40169|SELECT+MAX(Weight)++++++++AS+MaxWeight,
+++++++AVG(Circumference)+AS+AvgCircumference
FROM+++Cakes+|40170|返回|40171|%2B-----------%2B------------------%2B
%7C+MaxWeight+%7C+AvgCircumference+%7C
%2B-----------%2B------------------%2B
%7C++++++++80+%7C++++++++++13.325++%7C
%2B-----------%2B------------------%2B|40172|即使从技术上讲，不能肯定地说80是最重蛋糕的重量(因为未知的数目可能更大)，上述结果通常比简单地返回未知的结果更有用。|40173|%2B-----------%2B------------------%2B
%7C+MaxWeight+%7C+AvgCircumference+%7C
%2B-----------%2B------------------%2B
%7C+++++++++?+%7C++++++++++13.325++%7C
%2B-----------%2B------------------%2B|40174|因此，您很可能希望NULLs被忽略，而警告只是提醒您这一事实正在发生。|40175|entityMap|0|LINK|mutability|MUTABLE|url|https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-ver15|1|https://dba.stackexchange.com/questions/10952/meaning-of-set-in-error-message-null-value-is-eliminated-by-an-aggregate-or-o/18454#18454|2|https://feedback.azure.com/forums/908035-sql-server/suggestions/40496701-scalargbaggtotop-transformation-with-max-nullable|3|https://www.flickr.com/photos/8514720@N04/6862029495/|4|https://creativecommons.org/licenses/by/2.0/|5|IMAGE|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/8f44976e072755a3a88d10fb67348c9f.png|imageAlt^0|0|6|D|R|F|0|0|S|M|1H|1K|0|1P|4|9|9|1|23|2|2|0|0|0|J|L|4|S|4|1C|9|0|0|0|2|2|0|7|D|S|4|1I|4|0|0|9|B|2|3|E|4|4|0|0|0|1|5|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|2E|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|2F|8|@$E|2G|F|2H|G|H]]|9|@$E|2I|F|2J|1|2K]]|A|$]]|$1|I|3|J|5|D|7|2L|8|@$E|2M|F|2N|G|H]|$E|2O|F|2P|G|H]]|9|@]|A|$]]|$1|K|3|L|5|6|7|2Q|8|@$E|2R|F|2S|G|H]]|9|@$E|2T|F|2U|1|2V]|$E|2W|F|2X|1|2Y]]|A|$]]|$1|M|3|N|5|6|7|2Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|30|8|@$E|31|F|32|G|H]|$E|33|F|34|G|H]|$E|35|F|36|G|H]|$E|37|F|38|G|H]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|39|8|@]|9|@]|A|$]]|$1|S|3|T|5|U|7|3A|8|@]|9|@]|A|$V|W]]|$1|X|3|Y|5|6|7|3B|8|@$E|3C|F|3D|G|H]]|9|@]|A|$]]|$1|Z|3|10|5|6|7|3E|8|@$E|3F|F|3G|G|11]|$E|3H|F|3I|G|H]|$E|3J|F|3K|G|H]]|9|@]|A|$]]|$1|12|3|13|5|6|7|3L|8|@$E|3M|F|3N|G|11]]|9|@$E|3O|F|3P|1|3Q]|$E|3R|F|3S|1|3T]]|A|$]]|$1|14|3|15|5|6|7|3U|8|@]|9|@]|A|$]]|$1|16|3|17|5|18|7|3V|8|@]|9|@$E|3W|F|3X|1|3Y]]|A|$]]|$1|19|3|15|5|6|7|3Z|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|40|8|@]|9|@]|A|$]]|$1|1C|3|1D|5|U|7|41|8|@]|9|@]|A|$V|W]]|$1|1E|3|1F|5|6|7|42|8|@]|9|@]|A|$]]|$1|1G|3|1H|5|U|7|43|8|@]|9|@]|A|$V|W]]|$1|1I|3|1J|5|6|7|44|8|@]|9|@]|A|$]]|$1|1K|3|1L|5|U|7|45|8|@]|9|@]|A|$V|W]]|$1|1M|3|1N|5|6|7|46|8|@]|9|@]|A|$]]|$1|1O|3|1P|5|U|7|47|8|@]|9|@]|A|$V|W]]|$1|1Q|3|1R|5|6|7|48|8|@]|9|@]|A|$]]|$1|1S|3|-4|5|6|7|49|8|@]|9|@]|A|$]]]|1T|$1U|$5|1V|1W|1X|A|$1Y|1Z]]|20|$5|1V|1W|1X|A|$1Y|21]]|22|$5|1V|1W|1X|A|$1Y|23]]|24|$5|1V|1W|1X|A|$1Y|25]]|26|$5|1V|1W|1X|A|$1Y|27]]|28|$5|29|1W|2A|A|$2B|2C|2D|-4]]]]

Mostly you should do nothing about it.
<ul>
<li>It is possible to disable the warning by setting <code>ansi_warnings</code> off but this has <a href="https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-ver15" rel="noreferrer">other effects, e.g. on how division by zero is handled</a> and can cause failures when your queries use features like indexed views, computed columns or XML methods.</li>
<li>In some limited cases you can rewrite the aggregate to avoid it. e.g. <code>COUNT(nullable_column)</code> can be rewritten as <code>SUM(CASE WHEN nullable_column IS NULL THEN 0 ELSE 1 END)</code> but this isn't always possible to do straightforwardly without changing the semantics.</li>
</ul>
It's just an informational message <a href="https://dba.stackexchange.com/questions/10952/meaning-of-set-in-error-message-null-value-is-eliminated-by-an-aggregate-or-o/18454#18454">required in the SQL standard</a>. Apart from adding unwanted noise to the messages stream it has no ill effects (other than meaning that SQL Server can't just bypass reading <code>NULL</code> rows, which can have <a href="https://feedback.azure.com/forums/908035-sql-server/suggestions/40496701-scalargbaggtotop-transformation-with-max-nullable" rel="noreferrer">an overhead</a> but disabling the warning doesn't give better execution plans in this respect)
The reason for returning this message is that throughout most operations in SQL nulls propagate.
<code>SELECT NULL + 3 + 7</code> returns <code>NULL</code> (regarding <code>NULL</code> as an unknown quantity this makes sense as <code>? + 3 + 7</code> is also unknown)
but
<pre><code>SELECT SUM(N)
FROM (VALUES (NULL),
 (3),
 (7)) V(N) 
</code></pre>
Returns <code>10</code> and the warning that nulls were ignored.
However these are exactly the semantics you want for typical aggregation queries. Otherwise the presence of a single <code>NULL</code> would mean aggregations on that column over all rows would always end up yielding <code>NULL</code> which is not very useful.
Which is the heaviest cake below? (<a href="https://www.flickr.com/photos/8514720@N04/6862029495/" rel="noreferrer">Image Source</a>, <a href="https://creativecommons.org/licenses/by/2.0/" rel="noreferrer">Creative Commons</a> image altered (cropped and annotated) by me)
<a href="https://i.stack.imgur.com/z8fj6.png" rel="noreferrer"><img src="https://i.stack.imgur.com/z8fj6.png" alt="enter image description here" /></a>
After the third cake was weighed the scales broke and so no information is available about the fourth but it was still possible to measure the circumference.
<pre><code>+--------+--------+---------------+
| CakeId | Weight | Circumference |
+--------+--------+---------------+
| 1 | 50 | 12.0 |
| 2 | 80 | 14.2 |
| 3 | 70 | 13.7 |
| 4 | NULL | 13.4 |
+--------+--------+---------------+
</code></pre>
The query
<pre><code>SELECT MAX(Weight) AS MaxWeight,
 AVG(Circumference) AS AvgCircumference
FROM Cakes 
</code></pre>
Returns
<pre><code>+-----------+------------------+
| MaxWeight | AvgCircumference |
+-----------+------------------+
| 80 | 13.325 |
+-----------+------------------+
</code></pre>
even though technically it is not possible to say with certainty that 80 was the weight of the heaviest cake (as the unknown number may be larger) the results above are generally more useful than simply returning unknown.
<pre><code>+-----------+------------------+
| MaxWeight | AvgCircumference |
+-----------+------------------+
| ? | 13.325 |
+-----------+------------------+
</code></pre>
So likely you want NULLs to be ignored, and the warning just alerts you to the fact that this is happening.

blocks|key|929281|text|我认为在这种情况下您可以忽略这个警告，因为您使用了MIN函数。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|929282|“除计数外，聚合函数忽略空值”|BOLD|929283|请参阅聚合函数(Transact-SQL)|929284|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/en-us/library/ms173454.aspx^0|P|3|0|0|F|0|3|I|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@$9|W|A|X|B|H]]|D|@]|E|$]]|$1|I|3|J|5|6|7|Y|8|@]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|K|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

I think you can ignore this warning in the case since you using the <code>MIN</code> function.

"Except for COUNT, aggregate functions ignore null values"

Please refer <a href="http://technet.microsoft.com/en-us/library/ms173454.aspx" rel="nofollow">Aggregate Functions (Transact-SQL)</a>

blocks|key|929330|text|如果希望使聚合考虑null值，并将结果视为null，则可以使用：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|929331|SELECT+IIF(COUNT(N)+!=+COUNT(*),+NULL,+SUM(N))+as+[Sum]
FROM+++(VALUES+(NULL),
+++++++++++++++(3),
+++++++++++++++(7))+V(N)+|code-block|syntax|javascript|929332|如果没有给出所有值，则返回null。|929333|entityMap^0|9|4|L|4|0|0|D|4|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

If you want to make aggregates consider <code>null</code> values and treat the result as <code>null</code> you can use:

<pre><code>SELECT IIF(COUNT(N) != COUNT(*), NULL, SUM(N)) as [Sum]
FROM (VALUES (NULL),
 (3),
 (7)) V(N) 
</code></pre>

This returns <code>null</code> if not all values are given.

I have this schema

<pre><code>create table t(id int, d date) 

insert into t (id, d) values (1, getdate()), 
 (2, NULL)
</code></pre>

When doing

<pre><code>declare @mindate date 
select @mindate = min(d) from t
</code></pre>

I get the warning

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

Why and what can I do about it?

Getting warning: Null value is eliminated by an aggregate or other SET operation

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

我有这个模式create table t(id int, d date) insert into t (id, d) values (1, getdate()),                              (2, NULL)做的时候declare @mindate date    select @min...

问获得警告: Null值由聚合或其他设置操作消除。
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获得警告: Null值由聚合或其他设置操作消除。EN