为什么要警告 COUNT(col) 中的 NULL?
这有点哲学问题，我猜...
This is a bit of a philosophical question, I guess...
假设我在 SQL Server 中执行此查询:
Say I execute this query in SQL Server:
SELECT mygroup, COUNT(mycolumn)
FROM mytable
GROUP BY mygroup
计数列包含 NULL 并且 ANSI_WARNINGS 选项为 ON，所以我得到:
The counted column contains NULLs and the ANSI_WARNINGS option is ON, so I get:
警告:空值被聚合或其他 SET 消除操作.
Warning: Null value is eliminated by an aggregate or other SET operation.
我明白这意味着什么.有很多方法可以修复"这个警告.但是... 为什么它首先要抱怨?
I understand what this means. And there are lots of ways to 'fix' this warning. But... why is it complaining in the first place?
跳过任何空值是所有 COUNT(mycolumn) 都可以！如果我想计算所有行，包括该列的空值，我会使用 COUNT(*).我意图跳过空值不是很清楚吗?
Skipping any NULLs is all COUNT(mycolumn) does! If I wanted to count all rows, including NULLs for this column, I would have used COUNT(*). Isn't it clear that it's my intention to skip NULLs?
我猜 ANSI 标准要求发出此警告，即使在这种明显的情况下也是如此.为什么?
I guess the ANSI standard demands this warning, even in this obvious case. Why?
推荐答案
警告仅在聚合结果集中存在实际空值时出现.我相信它是为了提醒您不会计算空值，并在您遇到数据一致性问题时警告您，因为空值出现在您意想不到的地方.
The warning only appears when an actual null is present in the aggregate resultset. I believe it is there to remind you that nulls will not be counted and to warn you should you come across data consistency issues because a null appears where you did not expect.
相关文章