MySQL查询每个部门薪水TOP3

假设有两个表Department和Employee。

department的表结构如下：

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(60) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

employee表结构如下：

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| Id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name         | varchar(60) | YES  |     | NULL    |                |
| Salary       | double(9,2) | YES  |     | NULL    |                |
| DepartmentId | int(11)     | YES  | MUL | NULL    |                |

+--------------+-------------+------+-----+---------+----------------+

现在要查询每个部门的薪水TOP3，查询语句如下：

SELECT
	d. NAME Department,
	c. NAME Employee,
	c.Salary
FROM
	(
		SELECT
			b.DepartmentId,
			b.Salary,
			b. NAME,
			b.rank
		FROM
			(
				SELECT
					a.DepartmentId,
					a.Salary,
					a. NAME,

				IF (
					@pa = a.DepartmentId,

				IF (
					@pb = a.Salary ,@rank :=@rank ,@rank :=@rank + 1
				) ,@rank := 1
				) rank ,@pa := a.DepartmentId ,@pb := a.Salary
				FROM
					(
						SELECT
							@rank := 0,
							DepartmentId,
							Salary,
							NAME
						FROM
							Employee
						ORDER BY
							DepartmentId,
							Salary DESC
					) a
			) b
		HAVING
			rank <= 3
	) c
JOIN Department d ON (c.DepartmentId = d.Id)
ORDER BY
	c.DepartmentId,
	c.Salary DESC