SQL 为什么不能在单个 SELECT 中混合聚合值和非聚合值？

Why can't you mix Aggregate values and Non-Aggregate values in a single SELECT?

提问by TarkaDaal

I know that if you have one aggregate function in a SELECT statement, then all the other values in the statement must be either aggregate functions, or listed in a GROUP BY clause. I don't understand whythat's the case.

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

If I do:

如果我做：

SELECT Name, 'Jones' AS Surname FROM People

I get:

我得到：

NAME    SURNAME
Dave    Jones
Susan   Jones
Amy     Jones

So, the DBMS has taken a value from each row, and appended a single value to it in the result set. That's fine. But if that works, why can't I do:

因此，DBMS 从每一行中取出一个值，并在结果集中为其附加一个值。没关系。但如果这有效，为什么我不能这样做：

SELECT Name, COUNT(Name) AS Surname FROM People

It seems like the same idea, take a value from each row and append a single value. But instead of:

似乎是同一个想法，从每一行中取一个值并附加一个值。但不是：

NAME    SURNAME
Dave    3
Susan   3
Amy     3    

I get:

我得到：

You tried to execute a query that does not include the specified expression 'ContactName' as part of an aggregate function.

您尝试执行的查询不包含指定的表达式“ContactName”作为聚合函数的一部分。

I know it's not allowed, but the two circumstances seem so similar that I don't understand why. Is it to make the DBMS easier to implement? If anyone can explain to me why it doesn't work like I think it should, I'd be very grateful.

我知道这是不允许的，但这两种情况似乎如此相似，我不明白为什么。是为了让 DBMS 更容易实现吗？如果有人能向我解释为什么它不像我认为的那样工作，我将不胜感激。

采纳答案by Guffa

Aggregates doesn't work on a complete result, they only work on a group in a result.

聚合不适用于完整的结果，它们仅适用于结果中的一组。

Consider a table containing:

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

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

If you use a query that groups on Person, it will divide the data into these groups:

如果您使用对 Person 分组的查询，它会将数据分为以下几组：

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

If you use an aggreage, for exmple the count aggregate, it will produce one result for each group:

如果您使用聚合，例如计数聚合，它将为每个组生成一个结果：

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

So, the query select Person, count(*) from People group by Persongives you one record for each group:

因此，该查询select Person, count(*) from People group by Person为每个组提供一条记录：

Amy    3
Dave   1
Susan  2

If you try to get the Pet field in the result also, that doesn't work because there may be multiple values for that field in each group.

如果您还尝试在结果中获取 Pet 字段，那将不起作用，因为每个组中该字段可能有多个值。

(Some databases, like MySQL, does allow that anyway, and just returns any random value from within the group, and it's your responsibility to know if the result is sensible or not.)

（某些数据库，例如 MySQL，确实允许这样做，并且只返回组内的任何随机值，您有责任知道结果是否合理。）

If you use an aggregate, but doesn't specify any grouping, the query will still be grouped, and the entire result is a single group. So the query select count(*) from Personwill create a single group containing all records, and the aggregate can count the records in that group. The result contains one row from each group, and as there is only one group, there will be one row in the result.

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

回答by Dmitry

Think about it this way: when you call COUNT without grouping, it "collapses" the table to a single group making it impossible to access the individual items within a group in a select clause.

可以这样想：当您在不分组的情况下调用 COUNT 时，它会将表“折叠”为单个组，从而无法在 select 子句中访问组内的各个项目。

You can still get your result using a subquery or a cross join:

您仍然可以使用子查询或交叉联接获得结果：

    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

回答by ypercube??

As others explained, when you have a GROUP BYor you are using an aggregate function like COUNT()in the SELECTlist, you are doing a grouping of rows and therefore collapsing matching rows into one for every group.

正如其他人解释说，当你有一个GROUP BY，或者你使用的是聚合函数一样COUNT()在SELECT列表中，你正在做一个分组的行，因此崩溃匹配的行成一个为每个组。

When you only use aggregate functions in the SELECTlist, without GROUP BY, think of it as you have a GROUP BY 1, so all rows are grouped, collapsed into one. So, if you have a hundred rows, the database can't really show you a name as there are a hundred of them.

当你只在SELECT列表中使用聚合函数时，没有GROUP BY，把它想象成你有一个GROUP BY 1，所以所有的行都被分组，折叠成一个。所以，如果你有一百行，数据库就不能真正显示你的名字，因为有一百行。

However, for RDBMSs that have "windowing" functions, what you want is feasible. E.g. use aggregate functions without a GROUP BY.

但是，对于具有“窗口化”功能的 RDBMS，您想要的是可行的。例如，使用没有GROUP BY.

Example for SQL-Server, where all rows (names) in the table are counted:

SQL-Server 的示例，其中对表中的所有行（名称）进行计数：

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

How does the above work?

以上是如何工作的？

• It shows the Namelike the COUNT(*) OVER() AS cntdid not exist and

• It shows the COUNT(*)like if it was making a total grouping of the table.

• 它显示Name像 COUNT(*) OVER() AS cnt不存在和

• COUNT(*)如果它正在对表格进行总分组，它会显示类似的内容。

---

---

Another example. If you have a Surnamefield on the table, you can have something like this to show all rows grouped by Surname and counting how many people have same Surname:

另一个例子。如果您Surname在表上有一个字段，您可以使用这样的内容来显示按姓氏分组的所有行并计算有多少人具有相同的姓氏：

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

回答by GlennS

Your query implicitly asks for different types of rows in your result set, and that is not allowed. All rows returned should be of the same type and have the same kind of columns.

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

'SELECT name, surname' wants to returns a row for every row in the table.

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

'SELECT COUNT(*)' wants to return a single row combining the results of all the rows in the table.

'SELECT COUNT(*)' 想要返回结合表中所有行结果的单行。

I think you're correct that in this case the database could plausibly just do both queries and then copy the result of 'SELECT COUNT(*)' into every result. One reason for not doing this is that it would be a stealth performance hit: you'd effectively be doing an extra self-join without declaring it anywhere.

我认为您是正确的，在这种情况下，数据库可能只执行两个查询，然后将 'SELECT COUNT(*)' 的结果复制到每个结果中。不这样做的一个原因是，这会影响性能：您实际上是在执行额外的自联接，而无需在任何地方声明它。

Other answers have explained how to write a working version of this query, so I won't go into that.

其他答案已经解释了如何编写此查询的工作版本，因此我不会深入研究。

回答by Syjin

The aggregate function takes values from multiple rows with a specific condition and combines them into one value. This condition is defined by the GROUP BYin your statement. So you can't use an aggregate function without a GROUP BY

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

With

和

SELECT Name, 'Jones' AS Surname FROM People  

you simply select an additional column with a fixed value... but with

您只需选择一个具有固定值的附加列......但是

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

you tell the DBMS to select the Names, remember how often every Name occured in the table and collapse them into one row. So if you omit the GROUP BYthe DBMS can't tell, how to collapse the records

您告诉 DBMS 选择名称，记住每个名称在表中出现的频率并将它们折叠成一行。所以如果你省略了GROUP BYDBMS 无法分辨，如何折叠记录

回答by entonio

The aggregate function and the group by clause aren't separate things, they're parts of the same thing that appear in different places in the query. If you wish to aggregate on a column, you must say what function to use for aggregation; if you wish to have an aggregation function, it has to be applied over some column.

聚合函数和 group by 子句不是独立的东西，它们是出现在查询中不同位置的同一事物的一部分。如果你想在一个列上聚合，你必须说明用什么函数来聚合；如果您希望拥有聚合函数，则必须将其应用于某些列。