It's possible to have a WHERE clause after a H

问题:

Is it possible to use a WHERE clause after a HAVING clause?

The first thing that comes to my mind is sub queries, but I'm not sure.

P.S. If the answer is affirmative, could you give some examples?

回答1:

No, not in the same query.

The where clause goes before the having and the group by. If you want to filter out records before the grouping the condition goes in the where clause, and if you want to filter out grouped records the condition goes in the having clause:

select ...
from ...
where ...
group by ...
having ...

If neither of those are possible to use for some odd reason, you have to make the query a subquery so that you can put the where clause in the outer query:

select ...
from (
   select ...
   from ...
   where ...
   group by ...
   having ...
) x
where ...

回答2:

A HAVING clause is just a WHERE clause after a GROUP BY. Why not put your WHERE conditions in the HAVING clause?

回答3:

If it's a trick question, it's possible if the WHERE and the HAVING are not at the same level, as you mentionned, with subquery.

I guess something like that would work

HAVING value=(SELECT max(value) FROM foo WHERE crit=123)

p.s.: why were you asking? Do you have a specific problem?

p.s.s: OK silly me, I missed the "interview*" tag...

回答4:

From SELECT help

Processing Order of WHERE, GROUP BY, and HAVING Clauses The following steps show the processing order for a SELECT statement with a WHERE clause, a GROUP BY clause, and a HAVING clause:

The FROM clause returns an initial result set.

The WHERE clause excludes rows not meeting its search condition.

The GROUP BY clause collects the selected rows into one group for each unique value in the GROUP BY clause.

Aggregate functions specified in the select list calculate summary values for each group.

The HAVING clause additionally excludes rows not meeting its search condition.

So, no you can not.

回答5:

Within the same scope, answer is no. If subqueries is allowed then you can avoid using HAVING entirely.

I think HAVING is an anachronism. Hugh Darwen refers to HAVING as "The Folly of Structured Queries":

In old SQL, the WHERE clause could not be used on results of aggregation, so they had to invent HAVING (with same meaning as WHERE):

SELECT D#, AVG(Salary) AS Avg_Sal
  FROM Emp
 GROUP 
    BY D#
HAVING AVG(Salary) > 999;

But would we ever have had HAVING if in 1979 one could write:

SELECT * 
  FROM (
        SELECT D#, AVG(Sal) AS Avg_Sal
          FROM Emp
         GROUP 
            BY D# 
       )
      AS dummy
WHERE Avg_Sal > 999;

I strongly suspect the answer to Darwen's question is no.