Question

Oracle两表中得出高于某表的平均工资

SELECT AVG(e.sal),e.deptno FROM emp e GROUP BY e.deptno;
这个是得到按部门分组的各个部门平均分
SELECT e.empno,e.ename,e.sal,e.deptno FROM emp e ;
这个是得到员工的基本信息，e.sal是工资e.deptno是所属部门
我想要得到显示各个部门高于部门平均工资的人员信息

Answer 1

SELECT a.empno,a.ename,a.sal,a.deptno FROM emp a，
（SELECT AVG(e.sal) as sal,e.deptno FROM emp e GROUP BY e.deptno）b
where a.deptno =b.deptno and a.sal>b.sal

Answer 2

SELECT e.empno,e.ename,e.sal,e.deptno FROM emp e
left join (SELECT AVG(e.sal) ccc,e.deptno FROM emp e GROUP BY e.deptno)aaa on aaa.deptno=e.deptno
where aaa.ccc<e.sal