select id,name,dep_id,wages from emp a where a.id=
(select id from emp b where b.wages>(
select avg(wages) from emp c where b.dep_id=c.deptid group by dep_id));
select e.id, e.name, e.dep_id, a.avgwages from emp e,(select dep_id, avg(wages) avgwages from emp group by dep_id) a where a.dep_id=e.dep_id and e.wages>a.avgwavges;
主要考察Group by子句和嵌套查询两个知识点吧
你可以试试下面的代码。
SELECT name
FROM emp x
where
wages >(select avg(wages)
from emp y
where x.dep_id = y.dep_id
group by dep_id);
希望对你有所帮助~
select id, name ,dep_id from emp e1,dep d1 where e1.dep_id=d1.id and salary>(select avg(salary) avgsa,dep_idfrom emp e1,dep d1 where e1.dep_id=d1.id group by dep_id)
group by dep_id
select ename,job,sal from emp ,
(
select deptno,avg(sal) as avgsal from emp
group by deptno
) tmp_dept
where emp.deptno=tmp_dept.deptno and sal >tmp_dept.avgsal;