Oracle SQL Having Where Group by

以HR方案下的employees为例

(1),查询出部门平均薪水最高的部门里所有人的姓名,薪水,部门号.
14-MAR-12 HR @ hdb>desc employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

14-MAR-12 HR @ hdb>

select first_name,last_name,salary,department_id from employees
where department_id =
  (select e1.department_id
   from
  (select department_id,avg(salary) avgs from employees group by department_id) e1
   where  e1.avgs = (select max(e2.avgsalary)
                     from
                    (select avg(salary) avgsalary,department_id from employees group by department_id) e2
                    )
  );

FIRST_NAME           LAST_NAME                     SALARY DEPARTMENT_ID
-------------------- ------------------------- ---------- -------------
Steven               King                           25200            90
Neena                Kochhar                        17850            90
Lex                  De Haan                        17850            90

(2),where与having的使用
Group by按列进行分组处理,此时子句不能和where一起使用,只能与having一起使用;如果需要通过where子句实现条件筛选,只能重命名列,经过构建一个虚拟表的列实现.

having后面必须有函数

select department_id,avg(salary) avgs from employees group by department_id having avg(salary) > 7000;

where后面必须不能有函数

select * from
(select department_id,avg(salary) avgs from employees group by department_id)
where avgs > 7000;     

                                                                           

HAVING实现薪水最高的部门

SELECT avg(salary),department_id FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT max(avgs)          
                      FROM  
                     (SELECT AVG(salary) avgs FROM employees GROUP BY department_id)
                      );

WHERE实现薪水最高的部门

SELECT * FROM
(SELECT department_id,AVG(salary) avgs FROM employees GROUP BY department_id)
WHERE  avgs =(SELECT max(avgs)          
               FROM  
              (SELECT AVG(salary) avgs FROM employees GROUP BY department_id)
               );

(3),having子句一般与Group by一起使用,having后面定义的搜索条件一般是由group by产生的函数条件;having一般放在表达式最后.

(4),Group by
    不支持别名;
    select中出现的列必须以分组函数的方式出现,或者那列已包含在group by中;其他任何出现的列都是不允许的.