SQL 没有 GROUP BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6924896/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
HAVING without GROUP BY
提问by Adi
- Is the following possible according to standard(!) SQL?
- What minimal changes should be neccessary in order to be conforming to the standard (if it wasn't already)?
- It works as expected in MySQL, iff the first row has the maximum value for NumberOfPages.
- 根据标准（！）SQL，以下可能吗？
- 为了符合标准（如果还没有），应该进行哪些最小的更改？
- 它在 MySQL 中按预期工作，如果第一行具有NumberOfPages的最大值。
SELECT *
FROM Book
HAVING NumberOfPages = MAX(NumberOfPages)
SELECT *
FROM Book
HAVING NumberOfPages = MAX(NumberOfPages)
The following is written in the standard:
标准中写了以下内容：
HAVING <search condition>
- Let G be the set consisting of every column referenced by a <column reference> contained in the <group by clause>.
- Each column reference directly contained in the <search condition> shall be one of the following:
- An unambiguous reference to a column that is functionally dependent on G.
- An outer reference.
HAVING <search condition>
- 令 G 是由包含在 <group by 子句> 中的 <column reference> 所引用的每一列组成的集合。
- <search condition> 中直接包含的每个列引用应为以下之一：
- 对功能上依赖于 G 的列的明确引用。
- 外部参考。
Can somebody explain me, why it should be possibleaccording to the standard?
有人可以解释一下，为什么按照标准应该可以？
In MySQL, it perfectly works.
在 MySQL 中，它完美地工作。
回答by onedaywhen
Despite the Mimer Validatorresult, I don't believe yours is valid Standard SQL.
尽管Mimer Validator结果，我不相信你是有效的标准 SQL。
A HAVINGclause without a GROUP BYclause is valid and (arguably) useful syntax in Standard SQL. Because it operates on the table expression all-at-once as a set, so to speak, it only really makes sense to use aggregate functions. In your example:
没有HAVING子句的GROUP BY子句在标准 SQL 中是有效且（可以说）有用的语法。因为它将表表达式一次性作为一个集合进行操作，可以这么说，只有使用聚合函数才真正有意义。在你的例子中：
Book HAVING NumberOfPages = MAX(NumberOfPages)
is not valid because when considering the whole table, which row does NumberOfPagesrefer to? Likewise, it only makes sense to use literal values in the SELECTclause.
无效，因为在考虑整个表时，哪一行确实NumberOfPages引用了？同样，只有在SELECT子句中使用字面值才有意义。
Consider this example, which is valid Standard SQL:
考虑这个例子，它是有效的标准 SQL：
SELECT 'T' AS result
FROM Book
HAVING MIN(NumberOfPages) < MAX(NumberOfPages);
Despite the absence of the DISTINCTkeyword, the query will never return more than one row. If the HAVINGclause is satisfied then the result will be a single row with a single column containing the value 'T' (indicating we have books with differing numbers of pages), otherwise the result will be the empty set i.e. zero rows with a single column.
尽管没有DISTINCT关键字，查询永远不会返回多于一行。如果HAVING满足该子句，则结果将是单行，单列包含值“T”（表示我们有不同页数的书籍），否则结果将是空集，即零行与单列.
I think the reason why the query does not error in mySQL is due to propritary extensions that cause the HAVINGclause to (logically) come into existence after the SELECTclause (the Standard behaviour is the other way around), coupled with the implicit GROUP BYclause mentioned in other answers.
我认为 mySQL 中查询不会出错的原因是由于专有扩展导致该HAVING子句在子句之后（逻辑上）存在SELECT（标准行为是相反的），再加上GROUP BY其他中提到的隐式子句答案。
回答by Andrew
From the standard (bold added from emphasis)
来自标准（粗体从强调中添加）
1) Let HC be the having clause. Let TE be the table expression that immediately contains HC. If TE does not immediately contain a group by clause, then “GROUP BY ()” is implicit.Let T be the descriptor of the table defined by the GBC immediately contained in TE and let R be the result of GBC.
1) 让 HC 成为 having 子句。令 TE 成为立即包含 HC 的表表达式。如果 TE 没有立即包含 group by 子句，则“GROUP BY()”是隐式的。令 T 为立即包含在 TE 中的 GBC 定义的表的描述符，令 R 为 GBC 的结果。
With the implicit group by clause, the outer reference can access the TE columns.
使用隐式 group by 子句，外部引用可以访问 TE 列。
However, the certification to these standards is very much a self-certification these days, and the example you gave would not work across all of the main RDBMS providers.
但是，这些标准的认证如今在很大程度上是一种自我认证，您提供的示例不适用于所有主要的 RDBMS 提供商。
回答by Summer Wine
“When GROUP BY is not used, HAVING behaves like a WHERE clause.” The difference between where and having: WHERE filters ROWS while HAVING filters groups
“当不使用 GROUP BY 时，HAVING 的行为就像一个 WHERE 子句。” where 和 have 的区别：WHERE 过滤 ROWS 而 HAVING 过滤组
SELECT SUM(spending) as totSpending
FROM militaryspending
HAVING SUM(spending) > 200000;
Result
结果
totSpending
1699154.3
More detail, please consult https://dba.stackexchange.com/questions/57445/use-of-having-without-group-by-in-sql-queries/57453
更详细请参考 https://dba.stackexchange.com/questions/57445/use-of-have-without-group-by-in-sql-queries/57453
回答by Hrishikesh Kulkarni
Yes We can write the SQL query without Group by but write the aggregate function in our query.
是的，我们可以在没有 Group by 的情况下编写 SQL 查询，但在我们的查询中编写聚合函数。
select sum(Salary) from ibs having max(Salary)>1000
