最高薪酬员工+各部门平均薪酬

Question

员工属于一个部门（外键=D_ID)。员工有一个SSN（主键）、姓名、工资和D_ID。

SELECT 
    D.name, E.name, E.salary 
FROM 
    Employee E, Department D 
WHERE
    salary IN (SELECT MAX(E.salary) 
               FROM Employee E 
               GROUP BY E.D_ID)  
    AND E.D_ID = D.ID

SELECT AVG(E.salary) 
FROM Employee E 
GROUP BY E.D_ID

Answer 1

您可以为此使用窗口函数：

select department_name, employee_name, salary, avg_dept_salary 
from (
  select e.name as employee_name, 
         d.name as department_name, 
         e.salary,
         max(e.salary) over (partition by d.id) as max_dept_salary, 
         avg(e.salary) over (partition by d.id) as avg_dept_salary
  from Employee E
    join Department D on e.d_id = d.id
) t 
where salary = max_dept_salary
order by department_name;

以上是标准的ANSI SQL，运行在所有现代DBMS上。