您的初始查询缺少外部查询上的任何连接条件以及内部查询中的任何关联条件，这些条件会将其限制为仅关注部门的行。另外一般你不想
group byname为想必
id是主键。
解决这些问题以修复相关的子查询可以
SELECt e.employee_name, e.salary, d.department_nameFROM employee e JOIN department d ON d.department_id = e.department_idWHERe e.salary > (SELECt ROUND(AVG(Salary), 2) Dept_avg_sal FROM employee e2 WHERe e2.department_id = e.department_id)
但是，您可能会发现放弃标量相关子查询并替换为派生表会更好。
SELECt e.employee_name, e.salary, d.department_nameFROM employee e JOIN department d ON d.department_id = e.department_id JOIN (SELECt ROUND(AVG(Salary), 2) Dept_avg_sal, department_id FROM employee GROUP BY department_id) e2 ON e2.department_id = e.department_id AND e.salary > e2.Dept_avg_sal
对于Oracle，以下内容也应该有效
SELECt employee_name, salary, d.department_nameFROM (SELECt employee_name, salary, d.department_name, AVG(Salary) OVER (PARTITION BY e.department_id) AS AvgSalary FROM employee e JOIN department d ON d.department_id = e.department_id)WHERe salary > AvgSalary
