mysql - 在 SQL 中使用 group by 时如何返回多列

Question

我想要最高薪水低于 50,000 的员工姓名及其薪水和部门。我有以下内容

SELECT department, MAX(salary) as Highest salary
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000

如何获取要返回的员工姓名？

Answer 1

这么近...

SELECT department, name, MAX(salary) as Highest salary
FROM employees
GROUP BY department, name
HAVING MAX(salary) < 50000

评论更新后

SELECT name, department , salary
FROM employees e
   JOIN
  (
    SELECT department as dept, MAX(salary) as HighestSalary
    FROM employees
    GROUP BY department
  ) MaxE ON e.department = MaxE.dept AND e.salary = MaxE.HighestSalary