MySQL查询每个部门薪水TOP3

最新推荐文章于 2022-11-01 20:09:34 发布

原创最新推荐文章于 2022-11-01 20:09:34 发布 · 2.8k 阅读

2 ·

CC 4.0 BY-SA版权

sql 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一种复杂的SQL查询方法，用于从Employee表中找出每个部门薪酬最高的前三名员工，并展示了如何通过多层子查询实现这一目标。

假设有两个表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