分组函数

• max()、min()、sum()、avg()、count()
• ()里加入的是列名
• 分组函数默认不计null值
  null值需要加入nvl函数

select avg(nvl(comm,0))
from emp

• nvl函数（单行函数）
  nvl(列名,值)如果列名是null，就变为值
  在MYSQL里nvl替代为coalesce

练习1.1 查询部门20的员工，每个月的工资总和及平均工资。

select sum(sal), avg(sal)
from emp 
where deptno=20

1.2 查询工作在CHICAGO的员工人数，最高工资及最低工资。

select count(d.loc), max(e.sal), min(e.sal)
from emp e
join dept d on e.deptno=d.deptno 
where d.loc='CHICAGO'

1.3 查询员工表中一共有几种岗位类型。

select count(distinct nvl(d.dname,0))
from emp e
right outer join dept d on e.deptno=d.deptno 

• group by：select后只能加group by后面的列名
• 如果相加其他列，只能加入其它函数中
• group by出现的列不是必须出现在select中

练习2.1 查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。

select e.deptno, d.dname, count(e.deptno), max(e.sal), min(e.sal), sum(e.sal), avg(e.sal)
from emp e
join dept d on e.deptno=d.deptno
group by e.deptno, d.dname

2.2 查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。

select e.deptno, d.dname, e.job, count(e.deptno), max(e.sal),min(e.sal), sum(e.sal),avg(e.sal)
from emp e 
join dept d on e.deptno=d.deptno 
group by e.deptno, d.dname, e.job 

2.3 查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

select count(e.mgr), m.empno, m.ename
from emp e
right outer join emp m on e.mgr=m.empno
group by m.empno, m.ename 

• having后面加条件
• 跟where相比：可以加函数，where不行
• 6个子句的顺序

select deptno, count(deptno)
from emp
where deptno>10
group by deptno
having count(deptno) > 2
order by count(deptno)

练习3.1 查询部门人数大于2的部门编号，部门名称，部门人数。

select e.deptno, d.dname, count(e.deptno)
from emp e
join dept d on e.deptno=d.deptno 
group by e.deptno, d.dname
having count(e.deptno) > 2

3.2 查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。

select e.deptno, d.dname, count(e.deptno), avg(e.sal)
from emp e
join dept d on e.deptno=d.deptno 
group by e.deptno, d.dname 
having avg(e.sal)>2000 and count(e.deptno)>2
order by count(e.deptno) 

课后作业

1. 查询部门平均工资在2500元以上的部门名称及平均工资。

select d.dname, avg(e.sal)
from emp e
join dept d on e.deptno=d.deptno 
group by d.dname 
having avg(e.sal)>2500

2. 查询员工岗位中不是以“SA”开头并且平均工资在2500以上的岗位及平均工资，并按平均工资降序排序。

select e.job, avg(sal)
from emp e
group by e.job 
having e.job not like'SA%' and avg(sal)>2500
order by avg(sal) desc

3. 查询部门人数在2人以上的部门名称、最低工资、最高工资，并对求得的工资进行四舍五入到整数位。

select d.dname, round(min(e.sal)), round(max(e.sal))
from emp e
join dept d on e.deptno=d.deptno 
group by d.dname 
having count(e.deptno)>2 

4. 查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

select e.job, sum(e.sal) 
from emp e
group by e.job 
having e.job<>'SALESMAN' and sum(e.sal)>=2500

5. 显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。

select m.empno, m.ename, min(e.sal)
from emp e
left outer join emp m on e.mgr=m.empno 
group by m.empno, m.ename 
having min(e.sal)>=3000
order by min(e.sal) desc

6. 写一个查询，显示每个部门最高工资和最低工资的差额。

select e.deptno, max(e.sal)-min(e.sal)
from emp e
group by e.deptno

---

©code by sunjingbo