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;
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
主要考察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 name,wages,dep_id,avg(wages) from emp group by dep_id having wages>avg(wages)没测试过,不知对不对
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;
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 deptno,avg(sal) as avgsal from emp
group by deptno
) tmp_dept
where emp.deptno=tmp_dept.deptno and sal >tmp_dept.avgsal;
不过应该也不会报错吧,我的那条sql语句你的改改啊
字段都不一样的嘛,只是给你一个思路啊
group by dep_id
你可以试试下面的代码。
SELECT name
FROM emp x
where
wages >(select avg(wages)
from emp y
where x.dep_id = y.dep_id
group by dep_id);
希望对你有所帮助~
group by dep_id
having wages>avg(wages)没测试过,不知对不对
scott自带三张表:emp dept salgrade ,你看一下这个,很全。点这里
(select id from emp b where b.wages>(
select avg(wages) from emp c where b.dep_id=c.deptid group by dep_id));