想要精通算法和SQL的成长之路 - 部门工资最高的员工（SQL）

前言

想要精通算法和SQL的成长之路 - 系列导航

一. 部门工资最高的员工

原题链接

有一个员工表Employee，此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。表结构如下：

CREATE TABLE `Employee` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL,
  `departmentId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Employee_FK` (`departmentId`),
  CONSTRAINT `Employee_FK` FOREIGN KEY (`departmentId`) REFERENCES `Department` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

有一个部门表Department：

CREATE TABLE `Department` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

编写SQL查询以查找每个部门中薪资最高的员工。

首先我们可以写一个简单的left join，将部门和员工信息结合起来：

select 
	d.name as Department, e.name as Employee, e.salary as Salary
from 
	Employee e left join Department d 
on 
	(e.departmentId = d.id);

结果如下：

那么接下来就是在Employee，取出每个部门下工资最高的，那么这里肯定需要根据部门进行分组。即用到GROUP BY

SELECT departmentId, max(salary) FROM Employee GROUP BY departmentId

结果如下：

那么我们在第一个结果的基础上，只需要加一个where语句即可，让结果1在结果2的结果集中进行过滤。

where 
	(e.departmentId , e.salary) in (SELECT DepartmentId, max(Salary) FROM Employee GROUP BY DepartmentId)

那么最后合起来，完整的SQL如下：

select 
	d.name as Department, e.name as Employee, e.salary as Salary
from 
	Employee e left join Department d 
on 
	(e.departmentId = d.id)
where 
	(e.departmentId , e.salary) in (SELECT DepartmentId, max(Salary) FROM Employee GROUP BY DepartmentId) 

结果如下：

二. 部门工资前三高的所有员工

原题链接

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的高收入者是指一个员工的工资在该部门的不同工资中排名前三 。编写一个SQL查询，找出每个部门中收入高的员工 。

表结构和第一题一样，只不过在其基础上希望看排名前三的工资，并且工资要做到去重。那么我们在原本SQL的基础上只需要做两件事：

1. 工资的去重。
2. 取前三个。

首先，我们还是写一个简单的左联结查询：

select 
	d.name as Department, e.name as Employee, e.salary as Salary
from 
	Employee e left join Department d 
on 
	(e.departmentId = d.id) 

跟第一题一样，在这个查询的基础上，增加一个where子句，来做到筛选前三以及去重的一个目的。

竟然有排名的话，我们是不是可以写一个SQL，统计Employee中的每一条数据，看看有多少条员工的工资是比他高的。并且作为新的一列，那么又要做计数统计，又要对工资进行去重，那么就会涉及到两个函数或者关键字：

• count()
• DISTINCT

那么对应SQL就是：

select 
	e.* ,
	# 在e2这张表中，查找工资比当前数据大的条数。同时指定了部门是同一个，并且做到了去重操作
	(select COUNT(DISTINCT e2.salary) from Employee e2 where e.salary < e2.salary and e.departmentId = e2.departmentId ) as count
from 
	Employee e 

结果如下：

因为count计数是从0开始算的，并且我们只需要取前三的工资，那么我们就可以将上面的两个SQL进行整合，最终结果如下：

select 
	d.name as Department, e.name as Employee, e.salary as Salary
from 
	Employee e left join Department d 
on 
	(e.departmentId = d.id) 
where
	3 > (select COUNT(DISTINCT e2.salary) from Employee e2 where e.salary < e2.salary and e.departmentId = e2.departmentId )

运行结果：