SQL 在 HAVING 子句之后可以有 WHERE 子句吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1671968/
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
It's possible to have a WHERE clause after a HAVING clause?
提问by cc.
Is it possible to use a WHERE clause after a HAVING clause?
是否可以在 HAVING 子句之后使用 WHERE 子句？
The first thing that comes to my mind is sub queries, but I'm not sure.
我想到的第一件事是子查询，但我不确定。
P.S. If the answer is affirmative, could you give some examples?
PS如果答案是肯定的，你能举一些例子吗？
回答by Guffa
No, not in the same query.
不，不在同一个查询中。
The whereclause goes before the havingand the group by. If you want to filter out records before the grouping the condition goes in the whereclause, and if you want to filter out grouped records the condition goes in the havingclause:
该where子句位于having和之前group by。如果你想在分组之前过滤掉条件进入where子句的记录，如果你想过滤掉分组的记录条件进入having子句：
select ...
from ...
where ...
group by ...
having ...
If neither of those are possible to use for some odd reason, you have to make the query a subquery so that you can put the whereclause in the outer query:
如果出于某种奇怪的原因，这两个都不能使用，则必须使查询成为子查询，以便可以将该where子句放在外部查询中：
select ...
from (
select ...
from ...
where ...
group by ...
having ...
) x
where ...
回答by Nestor
A HAVING clause is just a WHERE clause after a GROUP BY. Why not put your WHERE conditions in the HAVING clause?
HAVING 子句只是 GROUP BY 之后的 WHERE 子句。为什么不将 WHERE 条件放在 HAVING 子句中？
回答by pascal
If it's a trick question, it's possible if the WHERE and the HAVING are not at the same level, as you mentionned, with subquery.
如果这是一个棘手的问题，那么正如您所提到的，如果 WHERE 和 HAVING 与子查询不在同一级别，则有可能。
I guess something like that would work
我想这样的事情会起作用
HAVING value=(SELECT max(value) FROM foo WHERE crit=123)
HAVING value=(SELECT max(value) FROM foo WHERE crit=123)
p.s.: why were you asking? Do you have a specific problem?
ps：你问这个干嘛？你有什么具体的问题吗？
p.s.s: OK silly me, I missed the "interview*" tag...
pss：好吧，我傻了，我错过了“采访*”标签......
回答by Adriaan Stander
From SELECT help
来自 SELECT 帮助
Processing Order of WHERE, GROUP BY, and HAVING Clauses The following steps show the processing order for a SELECT statement with a WHERE clause, a GROUP BY clause, and a HAVING clause:
The FROM clause returns an initial result set.
The WHERE clause excludes rows not meeting its search condition.
The GROUP BY clause collects the selected rows into one group for each unique value in the GROUP BY clause.
Aggregate functions specified in the select list calculate summary values for each group.
The HAVING clause additionally excludes rows not meeting its search condition.
WHERE、GROUP BY 和 HAVING 子句的处理顺序以下步骤显示了带有 WHERE 子句、GROUP BY 子句和 HAVING 子句的 SELECT 语句的处理顺序：
FROM 子句返回初始结果集。
WHERE 子句排除不满足其搜索条件的行。
GROUP BY 子句针对 GROUP BY 子句中的每个唯一值将选定的行收集到一个组中。
选择列表中指定的聚合函数计算每个组的汇总值。
HAVING 子句还排除不满足其搜索条件的行。
So, no you can not.
所以，不，你不能。
回答by onedaywhen
Within the same scope, answer is no. If subqueries is allowed then you can avoid using HAVINGentirely.
在同一范围内，答案是否定的。如果允许子查询，则可以HAVING完全避免使用。
I think HAVINGis an anachronism. Hugh Darwen refers to HAVINGas "The Folly of Structured Queries":
我认为HAVING是时代错误。Hugh Darwen 将其HAVING称为“结构化查询的愚蠢”：
In old SQL, the
WHEREclause could not be used on results of aggregation, so they had to inventHAVING(with same meaning asWHERE):SELECT D#, AVG(Salary) AS Avg_Sal FROM Emp GROUP BY D# HAVING AVG(Salary) > 999;But would we ever have had
HAVINGif in 1979 one could write:SELECT * FROM ( SELECT D#, AVG(Sal) AS Avg_Sal FROM Emp GROUP BY D# ) AS dummy WHERE Avg_Sal > 999;
在旧 SQL 中，该
WHERE子句不能用于聚合结果，因此他们不得不发明HAVING（与 含义相同WHERE）：SELECT D#, AVG(Salary) AS Avg_Sal FROM Emp GROUP BY D# HAVING AVG(Salary) > 999;但是，
HAVING如果在 1979 年有人可以这样写：SELECT * FROM ( SELECT D#, AVG(Sal) AS Avg_Sal FROM Emp GROUP BY D# ) AS dummy WHERE Avg_Sal > 999;
I strongly suspect the answer to Darwen's question is no.
我强烈怀疑 Darwen 问题的答案是否定的。
