sql - 连接表中的多个最大值

Question

我需要编写 SQL 查询，分别从每个部门获取薪酬最高的员工，并返回员工的姓名和薪酬以及他们所在的部门名称。

    employees
    +----+-------+------+---------------+
    | id | name  | pay  | department_id |
    +----+-------+------+---------------+
    | 1  | Bob   | 1200 | 1             |
    | 2  | Rob   | 600  | 2             |
    | 3  | Tom   | 800  | 2             |
    | 4  | Pam   | 900  | 1             |
    | 5  | Dave  | 1200 | 1             |
    +----+-------+------+---------------+

    departments
    +----+-----------+
    | id | name      |
    +----+-----------+
    | 1  | IT        |
    | 2  | Marketing |
    +----+-----------+

此查询仅返回表中每个部门中薪酬最高的第一位员工，但我想获得所有薪酬最高的员工，在本例中是 IT 部门的 Bob 和 Dave 以及市场营销部门的 Tom .

  SELECT d.name,e.name,e.pay FROM employees e JOIN departments d 
  ON e.department_id = d.id GROUP BY d.id HAVING MAX(e.pay)

正确的结果应该是:

+-----------+-------+------+
| IT        | Bob   | 1200 |
| IT        | Dave  | 1200 |
| Marketing | Tom   | 800  |
+-----------+-------+------+

Answer 1

“拥有最高薪水”的重新定义是:“应该没有人有更高的薪水”(同一部门内)

SELECT d.name, e.name,e.pay
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE NOT EXISTS (
    SELECT 42 FROM employees x
    WHERE x.department_id = e.department_id -- same dept
    AND x.pay > e.pay                       -- higher pay
    );