WHERE和HAVING有什么区别 [英] what is the difference between WHERE and HAVING
问题描述
我正在学习sql语法,但我听不懂.
i am learning sql syntax and i can't understand this.
问题的后半部分是一个技术性更高的问题.在WHERE和HAVING之间的数据库幕后到底发生了什么?哪个使用更多资源?它们是仅适用于不同数据集的相同算法吗?
the second half of the question is a much more technical one. what is actually happening behind the scenes of the database between WHERE and HAVING? which one uses more resources? are they same algorithm just applying to different data sets?
谢谢!
推荐答案
大多数查询中的位置并限制了查询所关注的记录.
Where is in most queries and limits the records that the query cares about.
具有用于分组依据"查询,并根据分组结果起作用.
Having is used in "Group By" queries, and acts upon the grouped results.
首先想到 Where
子句.如果db中有1000条记录,那么where子句可能使它只有80个问题.假设 Group By
子句将这80条db记录分组为(例如)15个聚合记录集行.如果没有Haveing子句,您将获得所有15个汇总行. Having
子句用于过滤这15个聚合行.
Think of the Where
clause as happening first. If there are 1000 records in the db, the where clause might make it so only 80 matter. And let's say that the Group By
clause groups these 80 db records into (for example) 15 aggregate recordset rows. Without the Having clause, you would get all 15 of these aggregate rows back. The Having
clause if for filtering upon these 15 aggregate rows.
比方说,您想要一个来自得克萨斯州的客户的清单,这些客户去年订购了5个以上订单.
Let's say you want a list of all the customers from Texas who made more than 5 orders last year.
-
Where
子句以获取来自德克萨斯州的人们去年所有的订单 -
分组依据
子句按客户对所有订单进行分组(在您的select子句中使用Count(OrderID)As OrderCount
) -
拥有
子句会将列出的客户限制为具有5个或更多订单的客户.
- the
Where
clause to get all the orders from last year by people from Texas - the
Group By
clause to group all the orders by customer (with aCount(OrderID) As OrderCount
in your select clause) - the
Having
clause would limit the customers listed to those having 5 or more orders.
这篇关于WHERE和HAVING有什么区别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!