来求一个sql查询语句
一个表employee中有姓名name,薪水salary,部门编号depno,一个表depart中有部门编号depid,部门名称depname。
输出所有薪水比该部门平均薪水高的人的姓名,薪水,部门名称
[解决办法]
WITH a1 AS
(
select depno,AVG(salary) AVG_salary
FROM employee
GROUP BY depno
)
SELECT a.NAME,a.salary,c.depname
FROM employee a
INNER JOIN a1 b ON a.depno=b.depno
INNER JOIN depart c ON a.depno=c.depno
WHERE a.salary>b.AVG_salary