SQL - 有 VS where

SQL - having VS where

提问by Adam Sh

I have the following two tables:

我有以下两个表：

1. Lecturers (LectID, Fname, Lname, degree).
2. Lecturers_Specialization (LectID, Expertise).

I want to find the lecturer with the most Specialization. When I try this, it is not working:

我想找到最专业的讲师。当我尝试这个时，它不起作用：

SELECT
  L.LectID, 
  Fname, 
  Lname 
FROM Lecturers L, 
     Lecturers_Specialization S
WHERE L.LectID = S.LectID
AND COUNT(S.Expertise) >= ALL (SELECT
  COUNT(Expertise)
FROM Lecturers_Specialization
GROUP BY LectID);

But when I try this, it works:

但是当我尝试这个时，它起作用了：

SELECT
  L.LectID,
  Fname,
  Lname 
FROM Lecturers L,
     Lecturers_Specialization S
WHERE L.LectID = S.LectID
GROUP BY L.LectID,
         Fname,
         Lname 
HAVING COUNT(S.Expertise) >= ALL (SELECT
  COUNT(Expertise)
FROM Lecturers_Specialization
GROUP BY LectID); 

What is the reason? Thanks.

是什么原因？谢谢。

回答by dasblinkenlight

WHEREclause introduces a condition on individual rows; HAVINGclause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiplerows. Your query calls for a second kind of condition (i.e. a condition on an aggregation) hence HAVINGworks correctly.

WHERE子句在单个行上引入条件；HAVING子句引入了聚合条件，即选择结果，其中从多行生成单个结果，例如计数、平均值、最小值、最大值或总和。您的查询需要第二种条件（即聚合条件），因此HAVING可以正常工作。

As a rule of thumb, use WHEREbefore GROUP BYand HAVINGafter GROUP BY. It is a rather primitive rule, but it is useful in more than 90% of the cases.

根据经验，使用WHEREbeforeGROUP BY和HAVINGafter GROUP BY。这是一个相当原始的规则，但它在 90% 以上的情况下都是有用的。

While you're at it, you may want to re-write your query using ANSI version of the join:

在此过程中，您可能希望使用 ANSI 版本的连接重新编写查询：

SELECT  L.LectID, Fname, Lname
FROM Lecturers L
JOIN Lecturers_Specialization S ON L.LectID=S.LectID
GROUP BY L.LectID, Fname, Lname
HAVING COUNT(S.Expertise)>=ALL
(SELECT COUNT(Expertise) FROM Lecturers_Specialization GROUP BY LectID)

This would eliminate WHEREthat was used as a theta join condition.

这将消除WHERE用作theta 连接条件的。

回答by Daniel Mann

HAVINGoperates on aggregates. Since COUNTis an aggregate function, you can't use it in a WHEREclause.

HAVING对聚合进行操作。由于COUNT是聚合函数，因此不能在WHERE子句中使用它。

Here'ssome reading from MSDN on aggregate functions.

这是MSDN 上关于聚合函数的一些读物。

回答by Pardhu

First we should know the order of execution of Clauses i.e FROM > WHERE > GROUP BY > HAVING > DISTINCT > SELECT > ORDER BY.Since WHEREClause gets executed before GROUP BYClause the records cannot be filtered by applying WHEREto a GROUP BYapplied records.

首先我们应该知道子句的执行顺序，即 FROM > WHERE > GROUP BY > HAVING > DISTINCT > SELECT > ORDER BY。由于WHERE子句被执行之前GROUP BY子句中的记录无法通过应用过滤WHERE到GROUP BY应用的记录。

"HAVING is same as the WHERE clause but is applied on grouped records".

“HAVING 与 WHERE 子句相同，但应用于分组记录”。

first the WHEREclause fetches the records based on the condition then the GROUP BYclause groups them accordingly and then the HAVINGclause fetches the group records based on the having condition.

首先WHERE子句根据条件获取记录，然后GROUP BY子句相应地对它们进行分组，然后HAVING子句根据具有条件获取组记录。

回答by Venkata Krishna Reddy

1. WHERE clause can be used with SELECT, INSERT, and UPDATE statements, whereas HAVING can be used only with SELECT statement.

2. WHERE filters rows before aggregation (GROUP BY), whereas HAVING filter groups after aggregations are performed.

3. Aggregate function cannot be used in WHERE clause unless it is in a subquery contained in HAVING clause, whereas aggregate functions can be used in HAVING clause.

1. WHERE 子句可以与 SELECT、INSERT 和 UPDATE 语句一起使用，而 HAVING 只能与 SELECT 语句一起使用。

2. WHERE 在聚合之前过滤行 (GROUP BY)，而 HAVING 在执行聚合之后过滤组。

3. 除非在包含在 HAVING 子句中的子查询中，否则不能在 WHERE 子句中使用聚合函数，而在 HAVING 子句中可以使用聚合函数。

Source

来源

回答by Nhan

Didn't see an example of both in one query. So this example might help.

在一个查询中没有看到两者的示例。所以这个例子可能会有所帮助。

  /**
INTERNATIONAL_ORDERS - table of orders by company by location by day
companyId, country, city, total, date
**/

SELECT country, city, sum(total) totalCityOrders 
FROM INTERNATIONAL_ORDERS with (nolock)
WHERE companyId = 884501253109
GROUP BY country, city
HAVING country = 'MX'
ORDER BY sum(total) DESC

This filters the table first by the companyId, then groups it (by country and city) and additionally filters it down to just city aggregations of Mexico. The companyId was not needed in the aggregation but we were able to use WHERE to filter out just the rows we wanted before using GROUP BY.

这首先按 companyId 过滤表，然后将其分组（按国家和城市），并另外将其过滤为仅墨西哥的城市聚合。聚合中不需要 companyId，但我们能够在使用 GROUP BY 之前使用 WHERE 过滤掉我们想要的行。

回答by Akash5288

You can not use where clause with aggregate functions because where fetch records on the basis of condition, it goes into table record by record and then fetch record on the basis of condition we have give. So that time we can not where clause. While having clause works on the resultSet which we finally get after running a query.

您不能将 where 子句与聚合函数一起使用，因为 where 根据条件获取记录，它会逐条记录到表中，然后根据我们提供的条件获取记录。所以那个时候我们可以不用where子句。虽然 have 子句适用于我们在运行查询后最终得到的结果集。

Example query:

示例查询：

select empName, sum(Bonus) 
from employees 
order by empName 
having sum(Bonus) > 5000;

This will store the resultSet in a temporary memory, then having clause will perform its work. So we can easily use aggregate functions here.

这会将 resultSet 存储在临时内存中，然后 have 子句将执行其工作。所以我们在这里可以很容易地使用聚合函数。

回答by Mihir Trivedi

1. We can use aggregate function with HAVING clause not by WHERE clause e.g. min,max,avg.

1. 我们可以使用带有 HAVING 子句而不是 WHERE 子句的聚合函数，例如 min,max,avg。

2. WHERE clause eliminates the record tuple by tuple HAVING clause eliminates entire group from the collection of group

2. WHERE 子句通过元组删除记录元组 HAVING 子句从组的集合中删除整个组

Mostly HAVING is used when you have groups of data and WHERE is used when you have data in rows.

当您有数据组时，主要使用 HAVING，当您有行数据时使用 WHERE。