找出每个部门不同工作岗位的最高薪资。
mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 10 | CLERK | 1300.00 |
| 10 | MANAGER | 2450.00 |
| 10 | PRESIDENT | 5000.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | MANAGER | 2975.00 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1600.00 |
+--------+-----------+----------+
找出每个部门的最高薪资，要求显示薪资大于2900的数据
方法一:mysql> select max(sal),deptno from emp group by deptno having max(sal) > 2900;（效率低）
方法二:mysql> select max(sal),deptno from emp where sal > 2900 group by deptno;
找去每个部门的平均薪资,要求显示薪资大于2000的数据；
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
