sql - 解释 DISTINCT COUNT(*) 的行为

Question

在回答另一个问题时，运行了一个查询，给了我一个意想不到的结果。将 COUNT 和 DISTINCT 组合为 COUNT(DISTINCT field) 以获取字段中非空不同值的数量是正常的。

我还尝试了 DISTINCT COUNT(field) 期望它向我显示“计数数量”，它基本上总是 1。但这不是它的作用。

CREATE TABLE Bob (id INT)
INSERT INTO Bob VALUES (0),(0),(1),(NULL)

SELECT COUNT(DISTINCT id) FROM Bob 
--Result: 2
SELECT COUNT(id) FROM Bob          
--Result: 3
SELECT DISTINCT COUNT(id) FROM Bob
--Result: 3
SELECT COUNT(*) FROM Bob
--Result: 4
SELECT DISTINCT COUNT(*) FROM Bob
--Result: 4

相反，当以这种方式使用时，查询引擎看起来好像简单地忽略了 DISTINCT。我针对 SQL Server、MySQL、Oracle、PostGreSQL 和 SQLite 对此进行了测试，其行为是相同的。

这是 SQL Server fiddle你好奇吗。

你能解释基于 ANSI 标准或其他一些历史惯例的行为吗？或者，也许我最初的预期行为在某些方面存在缺陷。

Answer 1

该查询实际上按预期工作，但一开始要理解原因有点棘手。

查看正在发生的事情的最佳方法是检查查询执行计划。

首先选择 Select Distinct Count。阅读计划，从右到左，您应该看到一个表扫描，有 4 行传递给 Stream Aggregate。从那里，您有一行传递给 Compute Scalar，其值为 4，因此 SQL 输出 4。

现在，看看 Select Count(distinct id)。再次从右到左，您应该看到表扫描，其中 4 行传递给 排序 .在 SQL 可以计算需要对它们进行排序的不同行之前，然后将 2 行传递给 Stream Aggregate，它传递的计数为 2。