group by关键字     *group by :分组由

  作用: 用于对于查询的数据进行分组并进行处理

  例如:select deptno ,job from emp group by deptno,job;

1.分组之后,不能够将除分组字段之外的字段之外的字段放在select后面(除了组函数)

  例如:select deptno from emp group by deptno;

2.group by后面可以跟多个字段,则这个多字段值都相同时,才分为一组

  例如:select deptno ,job from emp group by deptno,job;

3.分组后,可以用组函数对每个组进行数据处理

  例如:select deptno ,avg(sal) from emp group by;

      select deptno ,count(1)(sal) from emp group by;

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

having 关键字    *having :有

作用:用于对分组数据进行过滤

  例如:求平均薪水在2000以上的部门编号

    select deptno from emp group by deptno having avg(sal)>2000;

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

where 和 having关键字不冲突

  例如:select avg(sal),deptno from emp where sal >1500 group by deptno having avg(sal)> 2500 order by desc;

  或者:

    select avg(sal), deptno
    from emp
    where sal > 1500
    group by deptno
    having avg(sal) > 2500
    order by deptno desc;

  例如:select e.deptno from emp e where avg(sal) > 1500 group by deptno;

 

05-11 19:35