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;