为什么不能在单个SELECT语句中混合使用聚合值和非聚合值？

Question

我知道如果在SELECT语句中有一个聚合函数，那么语句中的所有其他值必须是聚合函数或列在GROUP BY子句中。我不明白为什么会这样。

例如：

SELECT Name, 'Jones' AS Surname FROM People

我的输出结果是:

NAME    SURNAME
Dave    Jones
Susan   Jones
Amy     Jones

因此，数据库管理系统从每行中取出一个值，并将单个值附加到结果集中。这很好。但如果这样有效，为什么我不能这样做：

SELECT Name, COUNT(Name) AS Surname FROM People

这似乎是相同的想法，从每一行中取一个值并添加单个值。但不是：

NAME    SURNAME
Dave    3
Susan   3
Amy     3    

我得到的错误信息是：

您尝试执行一个查询，但该查询未将指定的表达式“ContactName”作为聚合函数的一部分包含在内。

我知道这是不允许的，但这两种情况看起来非常相似，我不明白为什么不能这样做。这是为了使DBMS更容易实现吗？如果有人能向我解释为什么它不能按照我想象中的方式工作，我会非常感激。

- TarkaDaal

---

你认为在那个查询中 count(Name) 的含义是什么？ - Mat

一些关系型数据库管理系统（如SQL-Server、Postgres）具有“窗口”函数，可以显示类似于您所需的结果。当您使用GROUP BY时，无论是显式还是隐式地（通过在SELECT部分中使用聚合函数），都不能同时拥有两者。 - ypercubeᵀᴹ

我也建议看一下在一个OVER()子句中的PARTITION BY，它们往往在查询中更接近，事情可能就会开始变得清晰起来。有时候我希望SQL少一点结构化 :) - onedaywhen

Answer 1

聚合函数不适用于完整的结果集，它们仅适用于结果中的一个分组。

考虑一个包含以下内容的表：

Person   Pet
-------- --------
Amy      Cat
Amy      Dog
Amy      Canary
Dave     Dog
Susan    Snake
Susan    Spider

如果您使用以Person为分组依据的查询，它将把数据划分为这些组：

Amy:
  Amy    Cat
  Amy    Dog
  Amy    Canary
Dave:
  Dave   Dog
Susan:
  Susan  Snake
  Susan  Spider

如果你使用聚合函数，例如 count 聚合函数，它会为每个分组产生一个结果：

Amy:
  Amy    Cat
  Amy    Dog
  Amy    Canary    count(*) = 3
Dave:
  Dave   Dog       count(*) = 1
Susan:
  Susan  Snake
  Susan  Spider    count(*) = 2

因此，查询语句 select Person, count(*) from People group by Person 会为每个分组返回一条记录：

Amy    3
Dave   1
Susan  2

如果您尝试在结果中也获取Pet字段，那么这是行不通的，因为每个组中该字段可能有多个值。

（某些数据库（例如MySQL）仍允许这样做，并且仅返回该组中的任意值，您需要自行确定结果是否合理。）

如果您使用聚合函数但没有指定任何分组，查询仍将被分组，整个结果将是单个组。因此，查询select count(*) from Person将创建一个包含所有记录的单个组，并且聚合函数可以计算该组中的记录数。结果包含每个组的一行，由于只有一个组，因此结果中只有一行。

- Guffa

Answer 2

这样来想：当你在没有分组的情况下调用COUNT时，它会将表格“折叠”成一个单一的组，这使得在select子句中无法访问组内的个体项目。

您仍然可以使用子查询或交叉连接来获取结果：

    SELECT p1.Name, COUNT(p2.Name) AS Surname FROM People p1 CROSS JOIN People p2 GROUP BY p1.Name

    SELECT Name, (SELECT COUNT(Name) FROM People) AS Surname FROM People

- Dmitry

---

我认为他想要子查询为 (SELECT COUNT(p.Name) FROM People p WHERE p.Name = People.Name)，而不是整个计数。 - Sören Kuklau

小心使用CROSS JOIN；如果你将一个大表与另一个大表进行CROSS JOIN，结果会非常巨大（它会将table1的行数乘以table2的行数）。如果每个表都有10,000行，则CROSS JOIN将给出100,000,000行。我只在小型、固定的表格上使用CROSS JOIN，通常是一年中的月份列表。 - ZeroK

@ZeroK 幸运的是，关系型数据库管理系统足够聪明，可以优化执行计划。如果您查看 MS SQL 构建的第一个语句的实际执行计划，您会发现它在连接之前计算 COUNT 聚合。因此，在任何时候，您或 RDBMS 实际上都不必处理两个行集的笛卡尔积。 - Dmitry

Answer 3

正如其他人解释的那样，当你使用GROUP BY或在SELECT列表中使用聚合函数（如COUNT()）时，你正在对行进行分组，因此每个组都会折叠匹配的行。

当你在SELECT列表中仅使用聚合函数，而没有使用GROUP BY时，可以将其视为你有一个GROUP BY 1，因此所有行都被分组并折叠成一行。因此，如果你有100行，则数据库无法显示名称，因为有100个名称。

但是，对于具有“窗口”函数的关系型数据库管理系统，这是可行的。例如，在没有GROUP BY的情况下使用聚合函数。

以下是SQL Server的示例，其中计算表中所有行（名称）：

SELECT Name
     , COUNT(*) OVER() AS cnt
FROM People

上面的代码是如何工作的？

• 它显示了Name，就好像COUNT(*) OVER() AS cnt不存在一样。

• 它显示了COUNT(*)，就好像在对整个表进行分组计数。

---

另一个例子。如果您的表中有一个Surname字段，您可以使用以下内容显示按Surname分组的所有行，并计算具有相同Surname的人数：

SELECT Name
     , Surname
     , COUNT(*) OVER(PARTITION BY Surname) AS cnt
FROM People

- ypercubeᵀᴹ

Answer 4

您的查询隐式地要求结果集中有不同类型的行，这是不允许的。返回的所有行都应该是相同类型并且具有相同类型的列。

"SELECT name, surname" 想要为表中的每一行返回一行。

"SELECT COUNT（*）" 希望返回一个单独的行，合并表中所有行的结果。

我认为您是正确的，在这种情况下，数据库可能可以执行两个查询，然后将“SELECT COUNT（*）”的结果复制到每个结果中。不这样做的一个原因是它会带来潜在的性能影响：您实际上会执行一个额外的自连接，而没有在任何地方声明它。

其他答案已经解释了如何编写此查询的工作版本，所以我不再深入讲解。

- GlennS

---

+1。但是，COUNT(*)希望返回单个值。'Jones'也希望返回单个值。为什么一个可以，而另一个不行？ - TarkaDaal

Answer 5

聚合函数和group by子句不是独立的事物，它们是查询中出现在不同位置的同一部分。如果您想对某列进行聚合，则必须说明要使用哪个聚合函数；如果您希望有一个聚合函数，则必须将其应用于某些列。

- entonio

---

这并没有回答问题 - “为什么不能混合使用聚合值和非聚合值”。 - TarkaDaal

它回答了问题，即为什么引擎不允许您在没有分组的情况下进行计数。您应该能够弄清剩下的部分。虽然可能不是非常详尽或长，但这并不意味着它没有回答问题。 - entonio

它确实回答了“为什么引擎不允许您在没有分组的情况下进行计数”的问题。但这并不是“为什么不能混合聚合值和非聚合值”的问题？我不是说你错了 - 你显然是对的。但这不是我的问题的答案。 - TarkaDaal

Answer 6

聚合函数从具有特定条件的多个行中获取值，并将它们组合成一个值。这个条件由你语句中的GROUP BY定义。因此，如果没有GROUP BY，就不能使用聚合函数。

使用：

SELECT Name, 'Jones' AS Surname FROM People  

你只需选择一个带有固定值的额外列...但是

SELECT Name, COUNT(Name) AS Surname FROM People GROUP BY Name

你告诉数据库管理系统选择名称，记住每个名称在表中出现的频率并将它们折叠成一行。因此，如果省略GROUP BY，数据库管理系统无法确定如何折叠记录。

- Syjin