select a.empno,a.ename,a.deptno,a.salary from emp a where salary > (select avg(salary) from emp b where a.deptno = b.deptno)
--上面sql效率有点低,换个更好 select a.* from emp a, (select avg(b.salary) salary, b.deptno from emp b group by b.deptno ) c where a.salary > c.salary and a.deptno = c.deptno
--以及该部门高于平均工资的人数占到该部门的百分比 select t1.deptno, t1.count / t2.sumCount from (select count(a.empno) count,a.deptno from emp a where salary > (select avg(salary) from emp b where a.deptno = b.deptno) group by a.deptno) t1, (select count(e.empno) sumCount ,e.deptno from emp e,dept f where e.deptno = f.deptno group by e.deptno) t2where t1.deptno = t2.deptno
from emp a
where salary > (select avg(salary) from emp b where a.deptno = b.deptno)
select a.*
from emp a, (select avg(b.salary) salary, b.deptno from emp b group by b.deptno ) c
where a.salary > c.salary and a.deptno = c.deptno
--以及该部门高于平均工资的人数占到该部门的百分比
select t1.deptno, t1.count / t2.sumCount
from
(select count(a.empno) count,a.deptno
from emp a
where salary > (select avg(salary) from emp b where a.deptno = b.deptno)
group by a.deptno) t1,
(select count(e.empno) sumCount ,e.deptno
from emp e,dept f
where e.deptno = f.deptno
group by e.deptno) t2where t1.deptno = t2.deptno