求每个部门的工资最高的员工信息

1、表

1）、department表

mysql> desc Department
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select id,name from Department;
+----+-------+
| id | name  |
+----+-------+
|  1 | IT    |
|  2 | Sales |
+----+-------+
2 rows in set (0.00 sec)

mysql> 

2）、employee表

mysql> desc Employee
    -> ;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int         | NO   | PRI | NULL    |       |
| name         | varchar(50) | YES  |     | NULL    |       |
| salary       | int         | YES  |     | NULL    |       |
| departmentId | int         | YES  | MUL | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select id,name,salary,departmentId from Employee;
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
|  1 | Joe   |  85000 |            1 |
|  2 | Henry |  80000 |            2 |
|  3 | Sam   |  60000 |            2 |
|  4 | Max   |  90000 |            1 |
|  5 | Janet |  69000 |            1 |
|  6 | Randy |  85000 |            1 |
|  7 | Will  |  70000 |            1 |
+----+-------+--------+--------------+
7 rows in set (0.00 sec)

mysql> 

2、过程

方法一：

1）、先关联

mysql> select e.id id,e.name name,d.name department,salary
    -> from Employee e left join Department d 
    -> on e.departmentId=d.id;
+----+-------+------------+--------+
| id | name  | department | salary |
+----+-------+------------+--------+
|  1 | Joe   | IT         |  85000 |
|  2 | Henry | Sales      |  80000 |
|  3 | Sam   | Sales      |  60000 |
|  4 | Max   | IT         |  90000 |
|  5 | Janet | IT         |  69000 |
|  6 | Randy | IT         |  85000 |
|  7 | Will  | IT         |  70000 |
+----+-------+------------+--------+
7 rows in set (0.00 sec)

mysql> 

2）、求部门最高的员工（以部门id=1的为例）

mysql> select id,name,salary,departmentId
    -> from Employee
    -> where departmentId=1
    -> order by salary desc
    -> limit 1
    -> ;
+----+------+--------+--------------+
| id | name | salary | departmentId |
+----+------+--------+--------------+
|  4 | Max  |  90000 |            1 |
+----+------+--------+--------------+
1 row in set (0.00 sec)

mysql> 

3）组合：

mysql> select e.id,e.name as name,d.name as department,salary
    -> from Employee e left join Department d on e.departmentId=d.id
    -> where e.id in(
    -> select id
    -> from (select id
    -> from Employee e2
    -> where e2.departmentId =d.id
    -> order by salary desc
    -> limit 1
    -> ) as s
    -> )
    -> ;
+----+-------+------------+--------+
| id | name  | department | salary |
+----+-------+------------+--------+
|  2 | Henry | Sales      |  80000 |
|  4 | Max   | IT         |  90000 |
+----+-------+------------+--------+
2 rows in set (0.00 sec)

mysql> 

方法三、

利用开窗函数rank() over()结合partition by

mysql> select id,name,salary,departmentId,rank() over(partition by departmentId order by salary desc) as n from Employee;
+----+-------+--------+--------------+---+
| id | name  | salary | departmentId | n |
+----+-------+--------+--------------+---+
|  4 | Max   |  90000 |            1 | 1 |
|  1 | Joe   |  85000 |            1 | 2 |
|  6 | Randy |  85000 |            1 | 2 |
|  7 | Will  |  70000 |            1 | 4 |
|  5 | Janet |  69000 |            1 | 5 |
|  2 | Henry |  80000 |            2 | 1 |
|  3 | Sam   |  60000 |            2 | 2 |
+----+-------+--------+--------------+---+

 求出每个组的第一名：

mysql> select id,name,salary,departmentId
    -> from (
    -> select id,name,salary,departmentId,rank() over(partition by departmentId order by salary desc) as n from Employee
    -> )s
    -> where n=1;
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
|  4 | Max   |  90000 |            1 |
|  2 | Henry |  80000 |            2 |
+----+-------+--------+--------------+
2 rows in set (0.00 sec)

mysql> 

结合前面的left join

mysql> select e.id as id, e.name,d.name,salary
    -> from Employee e left join Department d on e.departmentId = d.id
    -> where e.id IN (
    -> select id
    -> from (
    -> select id,name,departmentId,salary,
    -> rank() over(PARTITION by departmentId ORDER BY salary desc) n
    -> from Employee)s 
    -> where n=1);
+----+-------+-------+--------+
| id | name  | name  | salary |
+----+-------+-------+--------+
|  4 | Max   | IT    |  90000 |
|  2 | Henry | Sales |  80000 |
+----+-------+-------+--------+
2 rows in set (0.00 sec)

mysql> 

方法三：

利用group by 和max

mysql> select departmentId,max(salary) from Employee group by departmentId;
+--------------+-------------+
| departmentId | max(salary) |
+--------------+-------------+
|            1 |       90000 |
|            2 |       80000 |
+--------------+-------------+
2 rows in set (0.00 sec)

mysql> 

结合前面的left join

mysql> select e.id as id, e.name,d.name,salary
    -> from Employee e left join Department d on e.departmentId = d.id
    -> where salary in(
    -> select max(salary)
    -> from Employee
    -> group by departmentId)
    -> ;
+----+-------+-------+--------+
| id | name  | name  | salary |
+----+-------+-------+--------+
|  2 | Henry | Sales |  80000 |
|  4 | Max   | IT    |  90000 |
+----+-------+-------+--------+
2 rows in set (0.00 sec)

mysql> 

参考了这篇文章如果有侵权，请告知，会及时下架