Oracle分组

group by 分组字段1,分组字段2…
SELECT语句格式：select 数据 from 数据源 where 行过滤条件 group by 分组字段 having 组过滤条件 order by 排序字段…;
执行流程: from – where – group by – having – select – roder by

-- 找出20部门和30部门的每个最高工资 
select deptno,max(sal) from emp group by deptno having deptno in(20,30);  --先分组后过滤
select deptno,max(sal) from emp where deptno in (20,30) group by deptno;  --先过滤后过滤


-- 求出每个部门的平均工资
select avg(sal),deptno from emp group by deptno;

-- 求出每个部门员工工资高于1000的的平均工资
select avg(sal),deptno from emp where sal>1000 group by deptno;

-- 求出平均工资高于2000的部门编号和平均工资
--求出每一个部门的平均薪资
select avg(sal),deptno from emp group by deptno;
--判断每一个平均薪资中哪一个平均薪资高于2000
select *
  from (select avg(sal) avg_sal, deptno from emp group by deptno)
 where avg_sal > 2000 order by deptno;