拥有和在哪里有什么区别?

I must be googling in the wrong way or I'm having a stupid moment in time.

我一定是在用谷歌搜索错误的方式，或者是我有一个愚蠢的时刻。

What's the difference between HAVING and WHERE in an SQL SELECT statement?

SQL SELECT语句中拥有和在哪里有什么区别?

EDIT: I've marked Steven's answer as the correct one as it contained the key bit of information on the link:

编辑:我把Steven的答案标记为正确答案，因为它包含了链接上的关键信息。

When GROUP BY is not used, HAVING behaves like a WHERE clause

当不使用GROUP BY时，具有类似WHERE子句的行为

The situation I had seen the WHERE in did not have GROUP BY and is where my confusion started. Of course, until you know this you can't specify it in the question.

我所看到的情况是，在没有团队的情况下，我的困惑开始了。当然，在你知道这个之前，你不能在问题中指定它。

Many thanks for all the answers which were very enlightening.

非常感谢所有的答案，这些答案都很有启发性。

20 个解决方案

#1

HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

为SELECT语句中使用的组或聚合函数指定搜索条件。

Source

源

#2

HAVING: is used to check conditions after the aggregation takes place.
WHERE: is used to check conditions before the aggregation takes place.

HAVING:用于在发生聚合之后检查条件。WHERE:用于在进行聚合之前检查条件。

This code:

这段代码:

select City, CNT=Count(1)
From Address
Where State = 'MA'
Group By City

Gives you a table of all cities in MA and the number of addresses in each city.

给你一张所有城市的表格和每个城市的地址。

This code:

这段代码:

select City, CNT=Count(1)
From Address
Where State = 'MA'
Group By City
Having Count(1)>5

Gives you a table of cities in MA with more than 5 addresses and the number of addresses in each city.

给出一个包含5个以上地址的MA城市的表格，以及每个城市的地址数量。

#3

Difference between WHERE and HAVING clause:

WHERE与have从句的区别:

1. WHERE clause can be used with - Select, Insert, and Update statements, where as HAVING clause can only be used with the Select statement.

1。WHERE子句可以与- Select、Insert和Update语句一起使用，而as have子句只能与Select语句一起使用。

2. WHERE filters rows before aggregation (GROUPING), where as, HAVING filters groups, after the aggregations are performed.

2。在聚合(分组)之前过滤行，在聚合执行之后过滤组。

3. Aggregate functions cannot be used in the WHERE clause, unless it is in a sub query contained in a HAVING clause, whereas, aggregate functions can be used in Having clause.

3所示。聚合函数不能在WHERE子句中使用，除非它位于包含在have子查询中的子查询中，而聚合函数可以在HAVING子句中使用。

Filtering Groups:

过滤组:

WHERE clause is used to filter rows before aggregation, where as HAVING clause is used to filter groups after aggregations

WHERE子句用于在聚合之前过滤行，在哪里as have子句用于在聚合之后过滤组

Select City, SUM(Salary) as TotalSalary
from tblEmployee
Where Gender = 'Male'
group by City
Having City = 'London'

In SQL Server we have got lot of aggregate functions. Examples

在SQL Server中，我们有很多聚合函数。例子

1. Count()
2. Count()
3. Sum()
4. Sum()
5. avg()
6. avg()
7. Min()
8. Min()
9. Max()
10. Max()

#4

Number one difference for me: if HAVING was removed from the SQL language then life would go on more or less as before. Certainly, a minority queries would need to be rewritten using a derived table, CTE, etc but they would arguably be easier to understand and maintain as a result. Maybe vendors' optimizer code would need to be rewritten to account for this, again an opportunity for improvement within the industry.

对我来说，第一个区别是:如果从SQL语言中删除了，那么生活将或多或少地继续下去。当然，少数查询需要使用派生表、CTE等进行重写，但结果可能更容易理解和维护。也许供应商的优化器代码需要重写以适应这种情况，这也是行业内改进的机会。

Now consider for a moment removing WHERE from the language. This time the majority of queries in existence would need to be rewritten without an obvious alternative construct. Coders would have to get creative e.g. inner join to a table known to contain exactly one row (e.g. DUAL in Oracle) using the ON clause to simulate the prior WHERE clause. Such constructions would be contrived; it would be obvious there was something was missing from the language and the situation would be worse as a result.

现在请考虑一下从语言中删除哪些内容。这一次，存在的大多数查询都需要重写，而不需要使用明显的替代结构。编码人员必须具有创造性，例如，使用ON子句来模拟before WHERE子句，将一个已知的只包含一行的表的内部连接(例如，Oracle中的DUAL)。这种结构将被设计出来;很明显，语言中缺少了一些东西，因此情况会更糟。

TL;DR we could lose HAVING tomorrow and things would be no worse, possibly better, but the same cannot be said of WHERE.

TL: DR .我们可能会失去明天，事情也不会更糟，甚至可能更好，但同样的事情在哪里也不会发生。

---

From the answers here, it seems that many folk don't realize that a HAVING clause may be used without a GROUP BY clause. In this case, the HAVING clause is applied to the entire table expression and requires that only constants appear in the SELECT clause. Typically the HAVING clause will involve aggregates.

从这里的答案来看，似乎很多人没有意识到“有”子句可以不用“一组一组”子句。在本例中，have子句应用于整个表表达式，并要求在SELECT子句中只显示常量。通常，have子句将包含聚合。

This is more useful than it sounds. For example, consider this query to test whether the name column is unique for all values in T:

这比听起来更有用。例如，考虑这个查询来测试name列对于T中的所有值是否唯一:

SELECT 1 AS result
  FROM T
HAVING COUNT( DISTINCT name ) = COUNT( name );

There are only two possible results: if the HAVING clause is true then the result with be a single row containing the value 1, otherwise the result will be the empty set.

只有两个可能的结果:如果HAVING子句是true，那么结果是包含值1的一行，否则结果将是空集。

#5

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

之所以将have子句添加到SQL，是因为WHERE关键字不能与聚合函数一起使用。

Check out this w3schools link for more information

查看w3schools链接以获得更多信息

Syntax:

语法:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

---

A query such as this:

这样的查询:

SELECT column_name, COUNT( column_name ) AS column_name_tally
  FROM table_name
 WHERE column_name < 3
 GROUP 
    BY column_name
HAVING COUNT( column_name ) >= 3;

...may be rewritten using a derived table (and omitting the HAVING) like this:

…可以使用派生表重写(省略了have)，如下所示:

SELECT column_name, column_name_tally
  FROM (
        SELECT column_name, COUNT(column_name) AS column_name_tally
          FROM table_name
         WHERE column_name < 3
         GROUP 
            BY column_name
       ) pointless_range_variable_required_here
 WHERE column_name_tally >= 3;

#6

The difference between the two is in the relationship to the GROUP BY clause:

两者之间的区别在于:

• WHERE comes before GROUP BY; SQL evaluates the WHERE clause before it groups records.

  群前在哪里;SQL在对记录进行分组之前计算WHERE子句。

• HAVING comes after GROUP BY; SQL evaluates HAVING after it groups records.

  在团体之后来的;SQL在对记录进行分组后进行评估。

References

引用

• SQLite SELECT Statement Syntax/Railroad Diagram

  SQLite选择语句语法/铁路图

• Informix SELECT Statement Syntax/Railroad Diagram

  Informix选择语句语法/铁路图

#7

HAVING is used when you are using an aggregate such as GROUP BY.

当您使用聚合(如GROUP BY)时，将使用HAVING。

SELECT edc_country, COUNT(*)
FROM Ed_Centers
GROUP BY edc_country
HAVING COUNT(*) > 1
ORDER BY edc_country;

#8

WHERE is applied as a limitation on the set returned by SQL; it uses SQL's built-in set oeprations and indexes and therefore is the fastest way to filter result sets. Always use WHERE whenever possible.

应用于SQL返回的集合的限制;它使用SQL的内置集合操作和索引，因此是过滤结果集的最快方式。尽可能在任何可能的地方使用。

HAVING is necessary for some aggregate filters. It filters the query AFTER sql has retrieved, assembled, and sorted the results. Therefore, it is much slower than WHERE and should be avoided except in those situations that require it.

对于一些聚合过滤器来说，拥有是必要的。它在sql检索、组装和排序结果后过滤查询。因此，它比应该避免的地方要慢得多，除非在需要它的情况下。

SQL Server will let you get away with using HAVING even when WHERE would be much faster. Don't do it.

SQL Server将允许您使用甚至在哪里更快的情况下使用。不要这样做。

#9

WHERE clause does not work for aggregate functions
means : you should not use like this bonus : table name

WHERE子句对聚合函数不起作用意味着:不应该像这样使用附注:表名

SELECT name  
FROM bonus  
GROUP BY name  
WHERE sum(salary) > 200  

HERE Instead of using WHERE clause you have to use HAVING..

这里不用WHERE子句，而要用have ..

without using GROUP BY clause, HAVING clause just works as WHERE clause

不使用GROUP BY子句，have子句就充当WHERE子句

SELECT name  
FROM bonus  
GROUP BY name  
HAVING sum(salary) > 200  

#10

When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.

当不使用GROUP BY时，WHERE和have子句实质上是等价的。

However, when GROUP BY is used:

但使用GROUP BY时:

• The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.
• WHERE子句用于从结果中筛选记录。过滤发生在任何分组之前。
• The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).
• have子句用于筛选组中的值(例如。，检查聚合到组之后的条件)。

Resource from Here

资源从这里

#11

Difference b/w WHERE and HAVING clause:

差异b/w在哪里和有条款:

The main difference between WHERE and HAVING clause is, WHERE is used for row operations and HAVING is used for column operations.

WHERE和have子句之间的主要区别是，WHERE用于行操作，而have用于列操作。

Why we need HAVING clause?

为什么我们需要有从句?

As we know, aggregate functions can only be performed on columns, so we can not use aggregate functions in WHERE clause. Therefore, we use aggregate functions in HAVING clause.

我们知道，聚合函数只能在列上执行，因此不能在WHERE子句中使用聚合函数。因此，我们在have子句中使用聚合函数。

#12

I had a problem and found out another difference between WHERE and HAVING. It does not act the same way on indexed columns.

我遇到了一个问题，发现了地点和拥有之间的另一个区别。它对索引列的作用不同。

WHERE my_indexed_row = 123 will show rows and automatically perform a "ORDER ASC" on other indexed rows.

其中my_indexed_row = 123将显示行，并在其他索引行上自动执行“ORDER ASC”。

HAVING my_indexed_row = 123 shows everything from the oldest "inserted" row to the newest one, no ordering.

my_indexed_row = 123显示了从最老的“插入”行到最新的行，没有排序。

#13

In an Aggregate query, (Any query Where an aggregate function is used) Predicates in a where clause are evaluated before the aggregated intermediate result set is generated,

在聚合查询(使用聚合函数的任何查询)中，在生成聚合中间结果集之前，对Where子句中的谓词进行计算，

Predicates in a Having clause are applied to the aggregate result set AFTER it has been generated. That's why predicate conditions on aggregate values must be placed in Having clause, not in the Where clause, and why you can use aliases defined in the Select clause in a Having Clause, but not in a Where Clause.

have子句中的谓词将在生成聚合结果集之后应用于聚合结果集。这就是为什么聚合值的谓词条件必须放在Having子句中，而不是Where子句中，以及为什么可以使用在have子句中的Select子句中定义的别名，而不是Where子句中。

#14

WHERE clause is used for comparing values in the base table, whereas the HAVING clause can be used for filtering the results of aggregate functions in the result set of the query Click here!

WHERE子句用于比较基表中的值，而have子句可以用于过滤查询结果集中的聚合函数的结果，点击这里!

#15

One way to think of it is that the having clause is an additional filter to the where clause.

一种考虑方法是，having子句是where子句的附加过滤器。

A WHERE clause is used filters records from a result. The filter occurs before any groupings are made. A HAVING clause is used to filter values from a group

使用WHERE子句过滤来自结果的记录。在进行任何分组之前都要进行筛选。have子句用于筛选组中的值

#16

The WHERE clause is evaluated before rows are grouped, and therefore is evaluated per row.

WHERE子句在对行进行分组之前进行计算，因此对每一行进行计算。

The HAVING clause is evaluated after rows are grouped, and therefore is evaluated per group.

在对行进行分组后，对HAVING子句进行评估，因此对每个组进行了评估。

#17

I use HAVING for constraining a query based on the results of an aggregate function. E.G. select * in blahblahblah group by SOMETHING having count(SOMETHING)>0

我使用have来约束基于聚合函数结果的查询。例如，在blahblahblahblah group中通过count(某物)>来选择*

#18

From here.

从这里。

the SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions

SQL标准要求必须只引用GROUP BY子句中的列或聚合函数中使用的列

as opposed to the WHERE clause which is applied to database rows

与应用于数据库行的WHERE子句相反

#19

While working on a project, this was also my question. As stated above, the HAVING checks the condition on the query result already found. But WHERE is for checking condition while query runs.

在做一个项目时，这也是我的问题。如上所述，have检查查询结果中已找到的条件。但是查询运行时检查条件在哪里?

Let me give an example to illustrate this. Suppose you have a database table like this.

让我举个例子来说明这一点。假设您有一个这样的数据库表。

usertable{ int userid, date datefield, int dailyincome }

usertable{int userid, date datefield, int dailyincome}

Suppose, the following rows are in table:

假设表中有以下几行:

1, 2011-05-20, 100

1,2011-05-20,100

1, 2011-05-21, 50

1、2011-05-21、50

1, 2011-05-30, 10

1,2011-05-30,10

2, 2011-05-30, 10

2,2011-05-30,10

2, 2011-05-20, 20

2,2011-05-20,20

Now, we want to get the userids and sum(dailyincome) whose sum(dailyincome)>100

现在，我们要得到userids和sum(dailyincome)的总和>100

If we write:

如果我们写:

SELECT userid, sum(dailyincome) FROM usertable WHERE sum(dailyincome)>100 GROUP BY userid

从usertable中选择userid, sum(dailyincome)，其中sum(dailyincome)>100组由userid

This will be an error. The correct query would be:

这将是一个错误。正确的查询是:

SELECT userid, sum(dailyincome) FROM usertable GROUP BY userid HAVING sum(dailyincome)>100

根据userid从usertable组中选择userid sum(日收入)，sum(日收入)>100

#20

It may be just that the subject of "where" is a row, whereas the subject of "having" is a group. Am I right?

可能只是“where”的主语是一行，而“have”的主语是一组。我说的对吗?