17,086
社区成员
发帖
与我相关
我的任务
分享
--以及该部门高于平均工资的人数占到该部门的百分比
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) t2
where t1.deptno = t2.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 a.empno,a.ename,a.deptno,a.salary
from emp a
where salary > (select avg(salary) from emp b where a.deptno = b.deptno)