MySQL 查询中 HAVING 和 WHERE 有什么区别？
我有一个基于某些表的联接的视图 (viewX)：
当我使用 WHERE 时，查询被延迟，处理器使用率达到 50%，最后我需要关闭并重启mysqld.exe服务再次尝试解决问题。
当我使用 HAVING 时，查询完美且快速地执行，我得到了结果，一切都准备好了。
查询类似于：
SELECT * FROM viewX WHERE column_of_view = 'foo'
SELECT * FROM viewX HAVING column_of_view = 'foo'
发生了什么？
我发现的解决方案是执行以下操作：
SELECT * FROM (SELECT * FROM viewX) as T WHERE column_of_view = 'foo'
SELECT * FROM (SELECT * FROM viewX) as T HAVING column_of_view = 'foo'
两个查询都工作正常，但是，我认为这很糟糕！ （从（...viewX）中选择*？？？）
I have a view (viewX) based on joins of some tables:
When I use WHERE, the query is delayed, processor usage goes to 50% and finally I need to close mysqld.exe service and restart to try to solve the problem again.
When I use HAVING, the query executes perfectly and quickly, I get the results and all is ready.
The query is similar to this:
SELECT * FROM viewX WHERE column_of_view = 'foo'
SELECT * FROM viewX HAVING column_of_view = 'foo'
What is happening?
The solution I found is to do something like this:
SELECT * FROM (SELECT * FROM viewX) as T WHERE column_of_view = 'foo'
SELECT * FROM (SELECT * FROM viewX) as T HAVING column_of_view = 'foo'
BOTH QUERIES WORKS FINE, BUT, I think this is BAD! (SELECT * FROM (...viewX)????)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
WHERE用于根据条件过滤查询结果。HAVING用于对聚合函数的结果应用过滤器。如果没有聚合函数，它的功能与 WHERE 相同。http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference- Between-having-and-where-clause/
WHEREis for filtering query results based on condition.HAVINGis for applying a filter on results of an aggregate function. In absence of aggregate function it functions same as WHERE.http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/
having 用于 sum、avg 等聚合函数，只能在 select 语句中使用。where 子句在聚合条件下不起作用
例如：其中 sum(mark) > 300 // 这不是真的
Having is used for aggregate functions like sum,avg so on and it can be using only in select statement.where clause won't work in aggregate conditions
eg: where sum(mark) > 300 // its not true
这取决于视图的定义 -having 子句应该只适用于聚合查询，并在分组之后应用。您看过两个查询计划（带解释）吗？
It depends on the definition of the view - having clause should only apply to an aggregate query and its applied after the grouping. Have you looked at the two query plans (with explain)?
列出时使用 WHERE，但没有可用的别名
HAVING 在列出所有可能的行后过滤行，以便生成别名
内联过滤行时应该会出现问题。
WHERE is used while listing and no ALIAS names are available yet
HAVING filters rows after listing all possible rows so ALIAS names are generated
There should be a problem while filtering rows inline.