今天带来两道类似的题目，难度渐进，如果能解出第一题，基本可以说sql功力可以了，如果第二题也能轻松做出来，那恭喜你，你已经是相当熟悉sql使用了。话不多说，进入正题

部门工资最高的员工

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+

拆解来看，先求最大薪资和其对应部门。

SELECT
	DepartmentId,
	Max(Salary) AS Salary
FROM
	Employee
GROUP BY
	1

这一步不难理解，然后再利用DepartmentId 和 Salary 找到对应的姓名和部门名称。

分解开，先找到员工名字。

SELECT
	a.*
FROM
	Employee AS a
INNER JOIN (
	SELECT
		DepartmentId,
		Max(Salary) AS Salary
	FROM
		Employee
	GROUP BY
		1
) b ON a.DepartmentId = b.DepartmentId
AND a.Salary = b.Salary

接下来再找到部门名字，就不难得出最终答案，如下：

SELECT
	d.NAME AS Department,
	c.NAME AS Employee,
	c.Salary AS Salary
FROM
	Department AS d
JOIN (
	SELECT
		a.*
	FROM
		Employee AS a
	INNER JOIN (
		SELECT
			DepartmentId,
			Max(Salary) AS Salary
		FROM
			Employee
		GROUP BY
			1
	) b ON a.DepartmentId = b.DepartmentId
	AND a.Salary = b.Salary
) c ON d.Id = c.DepartmentId

接下来加到一下难度，不仅仅要找出最高的，而是找出每个部门薪水前三的员工。

部门工资前三高的员工

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。

+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+

题目变化不大，如果没有第一题的铺垫，做起来还比较困难，但是我们刚完成第一题。于是只要解决获取最大的三个薪资即可。

这里使用Oracle的dense_rank可以很快做出来, 但mysql中没有现成的函数,这边参考前人的智慧, 用mysql实现了类似的功能.

有兴趣的可以了解一下 https://www.cnblogs.com/andysd/p/3668056.html

SELECT
			CASE
 WHEN @DepartmentId != DepartmentId THEN
 @dense_rank := 1
 WHEN @Salary = Salary THEN
 @dense_rank
 ELSE
 @dense_rank := @dense_rank + 1
 END AS dense_rank,
 id,
 @DepartmentId := DepartmentId AS DepartmentId,
 @Salary := Salary AS Salary
 FROM
 (SELECT @DepartmentId := 0) k,
 (SELECT @Salary := 0) v,
 (SELECT @dense_rank := 0) r,
 Employee e
 ORDER BY
 DepartmentId,
 Salary DESC

实际运行中发现存在问题。追踪代码之后发现为排序导致，此处为mysql优化器在捣鬼。解决方案参考： https://blog.csdn.net/l31299/article/details/79956629

SELECT
	CASE
WHEN @DepartmentId != DepartmentId THEN
	@dense_rank := 1
WHEN @Salary = Salary THEN
	@dense_rank
ELSE
	@dense_rank := @dense_rank + 1
END AS dense_rank,
 id,
 @DepartmentId := DepartmentId AS DepartmentId,
 @Salary := Salary AS Salary
FROM
	(SELECT @DepartmentId := 0) k,
	(SELECT @Salary := 0) v,
	(SELECT @dense_rank := 0) r,
	(
		SELECT
			*
		FROM
			Employee 
		ORDER BY
			DepartmentId ASC,
			Salary DESC limit 100000
	) e

之后就简单了，参考第一题的剩余步骤，得出以下答案

SELECT
	d. NAME AS Department,
	c. NAME AS Employee,
	c.Salary AS Salary
FROM
	Department AS d
JOIN (
	SELECT DISTINCT
		(a. NAME),
		a.Salary,
		a.DepartmentId
	FROM
		Employee AS a
	INNER JOIN (
		SELECT
			CASE
		WHEN @DepartmentId != DepartmentId THEN
			@dense_rank := 1
		WHEN @Salary = Salary THEN
			@dense_rank
		ELSE
			@dense_rank := @dense_rank + 1
		END AS dense_rank,
		id,
		@DepartmentId := DepartmentId AS DepartmentId,
		@Salary := Salary AS Salary
	FROM
		(SELECT @DepartmentId := 0) k,
		(SELECT @Salary := 0) v,
		(SELECT @dense_rank := 0) r,
		(
			SELECT
				*
			FROM
				Employee
			ORDER BY
				DepartmentId ASC,
				Salary DESC
			LIMIT 100000
		) e
	) b ON a.DepartmentId = b.DepartmentId
	AND a.Salary = b.Salary
	WHERE
		dense_rank <= 3
) c ON d.Id = c.DepartmentId

至此完成了此两题。过程相当艰辛。当然肯定还有其他解法，欢迎大家提出，交流带来进步。