SELECT * FROM EMPLOYEES WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT = EMPLOYEES.DEPARTMENT);优化该sql

可以使用JOIN来优化该SQL，避免使用子查询：

SELECT e1.*
FROM EMPLOYEES e1
JOIN (
SELECT DEPARTMENT, AVG(SALARY) AS AVG_SALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT
) e2
ON e1.DEPARTMENT = e2.DEPARTMENT AND e1.SALARY > e2.AVG_SALARY;

该SQL先通过子查询得到每个部门的平均工资，然后将结果与原始表连接，并筛选出薪资高于平均工资的员工。使用JOIN可以更高效地执行这个查询。