求部门工资最高的员工_20、求薪水最高的员工姓名-优快云博客

本文介绍了如何使用SQL查询从员工表中找出每个部门薪资最高的员工。提供了三种不同的查询脚本，包括使用子查询和内连接的方法，最终推荐使用脚本a来获取结果。这些查询涉及到了数据库的联接操作、聚合函数以及条件过滤，是数据库管理中的常见操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、求部门工资最高的员工，要求以列表形式给出查询结果，列字段名分别为：department，employee，salary

①建表语句

#部门表：department
CREATE TABLE department(
   id INT NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(id),
   NAME VARCHAR(10)
)

#员工表：employee
CREATE TABLE employee(
   id INT NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(id),
   NAME VARCHAR(10),
   salary DECIMAL(10,2),
   department_id INT DEFAULT 0
)

②初始化数据脚本

INSERT INTO department VALUES(1,'IT部'),(2,'财务部'),(3,'销售部');

INSERT INTO employee VALUES
(1,'jack',7000,1),
(2,'tom',8000,1),
(3,'joe',8000,1),
(4,'dell',9000,2),
(5,'ken', 10000,2),
(6,'tim',6000,3),
(7,'steven',7000,3);

③查询脚本

脚本a：
SELECT
d.name AS department,
e.name AS employee,
e.salary AS salary
FROM
employee e
INNER JOIN department d
ON e.department_id = d.id
WHERE (e.department_id, e.salary) IN
(SELECT
department_id,
MAX(salary)
FROM
employee
GROUP BY department_id);

脚本b：

SELECT
d.name AS department,
e.name AS employee,
c.max_salary AS salary
FROM
employee AS e
INNER JOIN
(SELECT
department_id,
MAX(salary) AS max_salary
FROM
employee
GROUP BY department_id) AS c
ON e.department_id = c.department_id
AND e.salary = c.max_salary
INNER JOIN department AS d
ON e.department_id = d.id;

脚本c：

SELECT
d.name AS department,
e.name AS employee,
c.max_salary AS salary
FROM
employee AS e,
department AS d,
(SELECT
department_id,
MAX(salary) AS max_salary
FROM
employee
GROUP BY department_id) AS c
WHERE e.department_id = c.department_id
AND e.salary = c.max_salary
AND e.department_id = d.id ;

④执行结果