MySQL练习题

1.取得每个部门最高薪水的人员名称

• 第一步：取得每个部门最高薪水。

mysql> select deptno,max(sal) as maxsal from emp group by deptno;
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+
3 rows in set (0.00 sec)

• 第二步：将以上结果当作临时表t，t表和emp e表示连接，条件是：t.deptno = e.deptno and t.maxsal = e.sal

mysql> 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.01 sec)

MySQL练习