MySQL查询每个部门的最高薪水

最新推荐文章于 2025-01-17 01:19:07 发布

delavior

最新推荐文章于 2025-01-17 01:19:07 发布

阅读量8.1k

点赞数 1

分类专栏： sql

本文链接：https://blog.youkuaiyun.com/delavior/article/details/44959465

版权

sql 专栏收录该内容

6 篇文章

订阅专栏

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

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

SELECT
	d. NAME Department,
	e. NAME Employee,
	e.Salary Salary
FROM
	Employee e
JOIN (
	SELECT
		MAX(Salary) max,
		DepartmentId
	FROM
		Employee
	GROUP BY
		DepartmentId
) g ON e.DepartmentId = g.DepartmentId
JOIN Department d ON (e.DepartmentId = d.Id)
WHERE
	e.Salary = g.max;