mysql例题

栗子

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