使用MYSQL的SQL查询

Question

编写一个查询，以显示每个部门的Department_ID大于10且最高薪资大于20000的所有员工的Department_ID和最高薪资。相对于Department_ID以降序显示数据。

select Department_ID, max(Salary)
from employees
where Department_ID > 10
having max(Salary) >20000
order by Department_ID DESC;

这是我的查询，但它只显示一个结果。由于问题涉及每个部门，我确信我的问题是不正确的。

Answer 1

您需要一个group by子句来使您的查询成为有效的聚合查询(在许多RDBMS中，您会得到一个语法错误)：

select department_id, max(salary) max_salary
from employees
where department_id > 10
group by department_id
having max(salary) > 20000

Answer 2

您缺少group by

   select Department_ID, max(Salary)
    from employees
    where Department_ID > 10
Group by Department_ID
    having max(Salary) >20000
    order by Department_ID DESC;