sql - 如何使用employee_id和emp_name选择每个部门的员工的最大（薪水）

Question

我想选择 emp_id,department_id,max(salary) 每个部门，但我使用 group by department_id 并且它有错误 ora-00979

3列在同一个表中（员工）

我该如何解决

select  department_id, employee_id as "ID",first_name || ' ' || last_name as "Name",max(salary)as "SALARY"
from EMPLOYEES
group by  department_id
order by department_id;

标签： sqloracle-sqldeveloper

Answer 1

您可以使用keep：

select  department_id,
        max(employee_id) keep (dense_rank first order by salary desc) as "ID",
        max(first_name || ' ' || last_name) keep (dense_rank first order by salary desc, employee_id desc) as "Name", 
        max(salary) as "SALARY"
from employees e
group by  department_id
order by department_id;