为什么要警告 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.

这篇关于为什么要警告 COUNT(col) 中的 NULL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！