SQL：使用WHERE AND而不是HAVING

here's an example of a SQL statement where we use HAVING:

这是我们使用HAVING的SQL语句的示例:

select column1 from table1
where condition1
having condition2;

isn't it the same exact thing if we do this:

如果我们这样做,是不是同样的事情:

select column1 from table1
where condition1 AND condition2;

what is the difference between these two?

这两者有什么区别?

9 个解决方案

#1

In your example, they should do the same thing. But WHERE gets processed before any GROUP BY, and so it doesn't have access to aggregated values (that is, the results of Min(), Max(), etc. functions). HAVING gets processed after GROUP BY and so can be used to constrain the result set to only those with aggregated values that match a certain predicate.

在你的例子中,他们应该做同样的事情。但是WHERE在任何GROUP BY之前被处理,因此它无法访问聚合值(即Min(),Max()等函数的结果)。在GROUP BY之后处理HAVING,因此可以将结果集限制为仅具有与特定谓词匹配的聚合值的结果集。

#2

HAVING is for use with aggregates, e.g., HAVING SUM(column1) > 200, WHERE is just for the columns, e.g., WHERE column1 < 20.

HAVING用于聚合,例如,HAVING SUM(column1)> 200,WHERE仅用于列,例如,WHERE column1 <20。

#3

No, they are completely different.

不,他们完全不同。

Having conditions are for grouping aggregate functions. They are computed after the aggregated value was computed.

有条件用于分组聚合函数。它们是在计算聚合值后计算的。

Example:

select id, count(1) 
  from table
 where COND1
 having count(1) > 1

Here, the having part is evaluated after the query computed the count(1) value for each group.

这里,在查询计算每个组的count(1)值之后评估having部分。

#4

In your example, it is the same because you have no GROUP BY

在您的示例中,它是相同的,因为您没有GROUP BY

Otherwise, HAVING is applied after GROUP BY which is applied after WHERE...

否则,在WHERE之后应用的GROUP BY之后应用HAVING ...

Saying that, HAVING with a simple filter (x = 2) is exactly the same as WHERE because x = 2 only has meaning if you grouped on it. You normally use HAVING on an aggregate (COUNT(*) > 2 for example) that can only be applied after GROUP BY

这么说,使用简单的过滤器(x = 2)进行HAVING与WHERE完全相同,因为如果你对它进行分组,x = 2只有意义。您通常在聚合(例如COUNT(*)> 2)上使用HAVING,只能在GROUP BY之后应用

#5

No, because having is for aggregate functions or group by clause.

不,因为有聚合函数或group by子句。

For example:

SELECT COUNT(ID)
FROM tablexpto
where name = 'a'
having count(ID) > 1

The first query would not run.

第一个查询不会运行。

#6

As others have (mostly) correctly stated, in SQL the WHERE clause is evaluated before the SELECT clause, therefore the result of a set function is 'out of scope' in the WHERE clause.

正如其他人(大多数)正确陈述的那样,在SQL中,WHERE子句在SELECT子句之前被计算,因此set函数的结果在WHERE子句中是“超出范围”。

For example, you CANNOT do this:

例如,你不能这样做:

SELECT Subject, MAX(Mark) AS TopScore
  FROM Exam_Marks
 GROUP 
    BY Subject
 WHERE TopScore <= 70;

because the column correlation name TopScore is not in scope for the WHERE clause.

因为列相关名称TopScore不在WHERE子句的范围内。

Of course we could use a subquery:

当然我们可以使用子查询:

SELECT DT1.TopScore
  FROM (
        SELECT Subject, MAX(Mark) AS TopScore
          FROM Exam_Marks
         GROUP 
            BY Subject
       ) AS DT1
 WHERE DT1.TopScore <= 70;

The problem was, early implementations of SQL (starting with IBM's System R) lacked support for derived tables, hence the unintuitive HAVING was born.

问题是,早期的SQL实现(从IBM的System R开始)缺乏对派生表的支持,因此非直观的HAVING诞生了。

You can read the whole sorry story in HAVING A Blunderful Time (or Wish You Were WHERE) by Hugh Darwen, from which I've borrowed the above examples.

你可以通过Hugh Darwen阅读整个令人遗憾的故事(或者希望你在哪里),我借用了上面的例子。

#7

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

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

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows.

HAVING子句类似于WHERE子句,但仅适用于作为整体的组,而WHERE子句适用于各个行。

Having http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/

#8

Having only works with a group by clause and limits records after they are grouped.

仅适用于group by子句,并在分组后限制记录。

#9

-1

to use having you need a group by clause. you will get an error without one

使用你需要group by子句。没有一个你会得到一个错误

#1

#2