LeetCode刷题之旅【数据库篇-5】 - 中等：部门工资最高的员工

最新推荐文章于 2022-04-28 17:57:48 发布

原创最新推荐文章于 2022-04-28 17:57:48 发布 · 380 阅读

0 ·

CC 4.0 BY-SA版权

LeetCode数据库专栏收录该内容

7 篇文章

订阅专栏

本文探讨了三种SQL查询方法，旨在找出每个部门中工资最高的员工，包括使用中间表进行过滤、内连接提高效率以及JOIN内连接的方法，对比了不同方法的执行效率。

2019年11月18日

题目部门工资最高的员工

解题1

使用中间表，根据departmentId来group by分组
两张表department和employee联表查询后，使用中间表作为where过滤条件

SELECT 
  Department.name AS 'Department',
	Employee.name AS 'Employee',
	Salary
 FROM employee 
join department 
on department.id = employee.DepartmentId
WHERE (employee.DepartmentId,employee.Salary) in (SELECT
    DepartmentId, MAX(Salary)
FROM
    Employee
GROUP BY DepartmentId);

解题2

WHERE内连接效率与官方题解相近


SELECT
    b.Name AS Department,
    a.Name AS Employee,
    c.Max_Salary AS Salary
FROM 
    Employee AS a,
    Department AS b,
    (
        SELECT DISTINCT DepartmentId, MAX(Salary) AS Max_Salary
        FROM Employee
        GROUP BY DepartmentId
    ) AS c
WHERE a.DepartmentId = c.DepartmentId
    AND a.DepartmentId = b.Id
    AND a.Salary = c.Max_Salary
;

解题3


-- JOIN内连接 效率最低
SELECT 
	b.`Name` as Department,
	a.`Name` as Employee,
	c.Max_Salary as Salary
FROM employee as a 
join (SELECT DISTINCT DepartmentId,MAX(Salary) AS Max_Salary FROM employee GROUP BY DepartmentId) as c 
on a.DepartmentId = c.DepartmentId and a.Salary = c.Max_Salary
JOIN department as b
on a.DepartmentId = b.Id
;