《Pro Oracle SQL》Chapter7 Advanced Grouping -- 7.2HAVING Clause

7.2 HAVING Clause     HAVING 子句   (page 195)

    Results generated by GROUP BY may be restricted by the criteria found in the HAVING clause.  The HAVING clause is quite versatile, resembling the WHERE clause in the conditions that may be used. Functions, operators, and subqueries may all be used in the HAVING clause. Listing 7-7 shows a query that will return all departments that have hired at least five employees since the beginning of the first full year after hiring began. 
由GROUP BY生成的结果集可以被HAVING子句中的标准所限制。HAVING子句非常“能干”，可以像条件中的WHERE子句那样使用。函数，操作符，以及子查询全都可以用于HAVING子句中。列表7-7展示一查询，返回至雇佣(工作)开始之后的第一个全年(下一年 )开始(到最后一个雇佣者受雇佣这段时间内)至少雇佣5名员工的所有部门。
    That the HAVING operation is executed after all data has been fetched can be seen as the FILTER in
step 1 of the execution plan shown in Listing 7-7.  Notice that an operator, a function, and subqueries
have all been used in the HAVING clause. 
HAVING操作执行于所有数据取出之后，可以从列表7-7所示执行计划的第一步的FILTER(筛选条件)看出。注意操作符，函数，或者子查询均可以用于HAVING子句。
Listing 7-7. HAVING Clause
  1  select /*+ gather_plan_statistics */
  2    d.dname
  3    , trunc(e.hiredate,'YYYY') hiredate 
  4    , count(empno) empcount
  5  from scott.emp e
  6  join scott.dept d on d.deptno = e.deptno
  7  group by d.dname, trunc(e.hiredate,'YYYY')
  8  having
  9    count(empno) >= 5
 10    and trunc(e.hiredate,'YYYY') between
 11      (select min(hiredate) from scott.emp)             
 12      and
 13      (select max(hiredate) from scott.emp)      --这个条件实际上总是成立
 14  order by d.dname;
 (这SQL看的怪难受的，如果习惯ORACLE风格的写法，可以看附件)
DNAME          HIREDATE              EMPCOUNT
-------------- ------------------- ----------
SALES          01/01/1981 00:00:00          6
 
1 row selected. 

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
| Id  | Operation                                                           | Name         | Starts   | E-Rows | A-Rows |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                  |      1     |                |      1      |
|*  1 |   FILTER                                                               |                  |      1     |                |      1       |
|   2 |      SORT GROUP BY                                           |                   |      1    |      1         |      6       |
|   3 |         MERGE JOIN                                               |                   |      1     |     14       |     14     |
|   4 |            TABLE ACCESS BY INDEX ROWID         | DEPT         |      1     |      4        |      4     |
|   5 |               INDEX FULL SCAN                                 | PK_DEPT     |      1      |      4       |      4      |
|*  6 |            SORT JOIN                                                |                   |      4      |     14       |     14    |
|   7 |               TABLE ACCESS FULL                            | EMP          |      1      |     14        |     14    |
|   8 |            SORT AGGREGATE                                  |                  |      1      |      1         |      1     |
|   9 |               TABLE ACCESS FULL                             | EMP         |      1       |     14        |     14    |
|  10 |           SORT AGGREGATE                                   |                  |      1      |      1         |      1     |
|  11 |              TABLE ACCESS FULL                            | EMP         |      1      |     14        |     14    |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 

   1 - filter((COUNT(*)>=5 AND TRUNC(INTERNAL_FUNCTION("E"."HIREDATE"),'fmyyyy')>= AND
              TRUNC(INTERNAL_FUNCTION("E"."HIREDATE"),'fmyyyy')<=))
   6 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
 
37 rows selected.