栗子
1. 取得每个部门最高薪水的人员名称
第一步:取每个部门最大薪水
select deptno,max(sal) as maxsal
from emp
group by deptno;
+--------+---------+
| deptno | maxsal |
+--------+---------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+---------+
3 rows in set (0.06 sec)
第二步:将上面的结果当成一张表t
select e.ename,t.*
from (select deptno,max(sal) as maxsal
from emp
group by deptno) t
join emp e
on t.deptno = e.deptno and t.maxsal = e.sal;
+-------+--------+---------+
| ename | deptno | maxsal |
+-------+--------+---------+
| BLAKE | 30 | 2850.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 5000.00 |
| FORD | 20 | 3000.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)
2. 哪些人的薪水在部门的平均薪水之上
第一步:每个部门的平均薪水
select deptno,avg(sal) as avgsal
from emp
group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.02 sec)
第二步:将上表结果当成临时表t