mysql - 无法识别使用 MAX 和 OVER PARTITION BY 的语法问题

Question

我正在尝试回答中等难度的 SQL Leetcode 问题。问题是要我找到每个部门薪水最高的人。

我尝试使用 GROUP BY 但无法找出该方法，因此我尝试使用 PARTITION BY 来查找每个部门的最高工资。之后，我会添加一个最终的 WHERE 语句，在其中我可以筛选出工资等于最高工资的人员。

这是我到目前为止的查询:

SELECT 
    Department.Name AS Department, 
    Employee.Name AS Employee, 
    Employee.Salary,
    MAX(Employee.Salary) OVER (PARTITION BY Department.Name) AS MaxSalary
FROM Employee
LEFT JOIN Department ON Department.id = Employee.DepartmentId
;

有两张 table 。

EMPLOYEE TABLE:

+----+-------+--------+--------------+
| 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 TABLE:

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

正确的输出应该是这样的。

CORRECT EXPECTED OUTPUT:

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

运行当前查询会导致运行时错误并显示以下消息:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY Department.Name) AS MaxSalary
FROM Employee
LEFT JOIN Department O' at line 7

我已经多次查看了语法，因此我认为这是对 PARTITION BY 工作原理的错误理解。我期望我能看到每个人的右侧列出的每个部门的最高工资。像这样的事情:

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

实现这一目标后，我将添加这一行:

WHERE Employee.Salary = MaxSalary;

执行此操作的正确方法是什么？

Answer 1

您可以考虑使用以下其中一项:

SELECT D.Name AS Department, E.Name AS Employee, E.Salary
FROM Employee AS E LEFT JOIN Department AS D ON E.DepartmentId = D.id
WHERE (E.Salary = (SELECT MAX(X.Salary) FROM Employee AS X WHERE (X.DepartmentId = E.DepartmentId)));

SELECT D.Name AS Department, E.Name AS Employee, E.Salary
FROM Employee AS E LEFT JOIN Department AS D ON E.DepartmentId = D.id
INNER JOIN (SELECT DepartmentId, MAX(Salary) AS MaxSalary FROM Employee GROUP BY DepartmentId) AS X ON E.DepartmentId = X.DepartmentId AND E.Salary = X.MaxSalary;