MySQL部门工资最高的员工

2024-01-02 17:41| 来源: 网络整理| 查看: 265

MySQL部门工资最高的员工 SQL架构题目描述题解方法：使用 JOIN 和 IN 语句方法2方法： GROUP BY 语句知识点exists 和 in1.原理2.分析3.总结4.效率5.举例说明

SQL架构 Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int); Create table If Not Exists Department (Id int, Name varchar(255)); insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '70000', '1'); insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Jim', '90000', '1'); insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Henry', '80000', '2'); insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Sam', '60000', '2'); insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Max', '90000', '1'); insert into Department (Id, Name) values ('1', 'IT'); insert into Department (Id, Name) values ('2', 'Sales'); 题目描述

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

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

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

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

编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

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

解释：

Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

题解方法：使用 JOIN 和 IN 语句

算法

因为 Employee 表包含 Salary 和 DepartmentId 字段，我们可以以此在部门内查询最高工资。

SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId;

注意：有可能有多个员工同时拥有最高工资，所以最好在这个查询中不包含雇员名字的信息。

+--------------+-------------+ | DepartmentId | MAX(Salary) | +--------------+-------------+ | 1 | 90000 | | 2 | 80000 | +--------------+-------------+ 2 rows in set (0.00 sec)

然后，我们可以把表 Employee 和 Department 连接，再在这张临时表里用 IN 语句查询部门名字和工资的关系。

SELECT Department.name AS 'Department', Employee.name AS 'Employee', Salary FROM Employee JOIN Department ON Employee.DepartmentId = Department.Id WHERE (Employee.DepartmentId , Salary) IN ( SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId ); +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Jim | 90000 | | Sales | Henry | 80000 | | IT | Max | 90000 | +------------+----------+--------+ 3 rows in set (0.00 sec) 方法2 select bb.Name as Department, aa.Employee, aa.Salary from ( select a.Id, a.Name as Employee, a.Salary, a.DepartmentId from Employee a join ( select DepartmentId, max(Salary) as max_sal from Employee group by DepartmentId ) b on a.DepartmentId = b.DepartmentId and a.Salary = b.max_sal ) aa join Department bb on aa.DepartmentId = bb.Id; +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Jim | 90000 | | Sales | Henry | 80000 | | IT | Max | 90000 | +------------+----------+--------+ 3 rows in set (0.01 sec) 方法： GROUP BY 语句 SELECT Department.NAME AS Department, Employee.NAME AS Employee, Salary FROM Employee, Department WHERE Employee.DepartmentId = Department.Id AND ( Employee.DepartmentId, Salary ) IN (SELECT DepartmentId, max( Salary ) FROM Employee GROUP BY DepartmentId ); +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Jim | 90000 | | Sales | Henry | 80000 | | IT | Max | 90000 | +------------+----------+--------+ 3 rows in set (0.00 sec) SELECT D.Name AS Department, E1.Name AS Employee, E1.Salary FROM Employee AS E1 INNER JOIN Department AS D ON E1.DepartmentId = D.Id WHERE NOT EXISTS (SELECT * FROM Employee AS E2 WHERE E1.DepartmentId = E2.DepartmentId AND E1.Salary

【本文地址】

公司简介

联系我们