找出所有部门中工资最高的员工信息

首先看数据库表的设计，很简单

+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name    | char(10) | YES  |     | NULL    |       |
| sal     | int(11)  | YES  |     | NULL    |       |
| dept_id | int(11)  | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+

mysql>select*from demo;
+---------+---------+---------+
| name    | sal     | dept_id |
+---------+---------+---------+
| dd      |33|1|
| dd      |330|1|
| ddx     |330|2|
| ddx1    |3300|2|
| ddx2    |3300|2|
| ddx3    |3300|3|
| zhaolei |333|5|
| jack    |3334|5|
| hello   |5555555|1|
+---------+---------+---------+
9 rows inset (0.00 sec)

我们可以分两步查询，第一步找出所有部门的最大工资，按照部门分组。

mysql>select dept_id,max(sal) as max_sal from demo groupby dept_id;
+---------+---------+
| dept_id | max_sal |
+---------+---------+
|1|5555555|
|2|3300|
|3|3300|
|5|3334|
+---------+---------+
4 rows inset (0.00 sec)

第二步，将查询结果作为一个临时表与原表进行连接，查询员工信息

mysql>select demo1.name,demo1.sal,demo1.dept_id from demo demo1,(select dept_id
,max(sal) as max_sal from demo groupby dept_id) demo2 where demo1.dept_id=demo2
.dept_id and demo1.sal=demo2.max_sal;
+-------+---------+---------+
| name  | sal     | dept_id |
+-------+---------+---------+
| ddx1  |3300|2|
| ddx2  |3300|2|
| ddx3  |3300|3|
| jack  |3334|5|
| hello |5555555|1|
+-------+---------+---------+
5 rows inset (0.00 sec)

也可以用外连接，如下面所示：

mysql>select demo1.name,demo1.sal,demo1.dept_id from demo demo1
    ->rightjoin (select dept_id,max(sal) as max_sal from demo groupby dept_id) demo2
    ->on demo1.dept_id=demo2.dept_id and demo1.sal=demo2.max_sal;
+-------+---------+---------+
| name  | sal     | dept_id |
+-------+---------+---------+
| hello |5555555|1|
| ddx1  |3300|2|
| ddx2  |3300|2|
| ddx3  |3300|3|
| jack  |3334|5|
+-------+---------+---------+
5 rows inset (0.00 sec)