在mysql中实现Window函数时遇到语法错误

Question

我正在尝试执行以下窗口函数的实现

SELECT employee_no, employee_name, department_no, 
round(avg(salary) OVER(PARTITION BY e.department_no),2) AS dept_avg_salary, 
salary as salary_of_employee 
FROM employee e 
WHERE e.department_no is not null and salary > (select avg(salary) 
from employee e2 
where e2.department_no = e.department_no 
groupby e2.department_no
);

遇到错误1064。

请提供帮助！

Answer 1

over是avg()的一部分，而不是round()。因此，您需要将其表示为：

round(avg(salary) OVER (PARTITION BY e.department_no), 2)

Answer 2

由于Gordon已经更正了语法错误，所以我会更正子查询：

select avg(e2.salary) 
from employee e2 
where e2.department_no = e.department_no 

您已经将子查询与外部查询关联，因此不需要使用GROUP BY。

因此，where和sub-query的最终子句将是：

WHERE e.salary > (select avg(e2.salary) 
from employee e2 
where e2.department_no = e.department_no 
);