求出工资比我当前员工高出500以外的人员个数。
求出每个部门工资高于本部门工资品均值的员工
我写得第二个:(结果出来有很多重复显示的行)
select empno,ename,emp.deptno,sal ,avg(sal) over(partition by emp.deptno)
from emp ,(select deptno,avg(sal) over(partition by deptno) avgsal from emp) deptavg
where sal>deptavg.avgsal
求出每个部门工资高于本部门工资品均值的员工
我写得第二个:(结果出来有很多重复显示的行)
select empno,ename,emp.deptno,sal ,avg(sal) over(partition by emp.deptno)
from emp ,(select deptno,avg(sal) over(partition by deptno) avgsal from emp) deptavg
where sal>deptavg.avgsal
select empno, ename, deptno, sal,
(select count(*) from emp emp1 where emp1.sal>(emp.sal+500))
from emp;--2)求出每个部门工资高于本部门工资品均值的员工
select empno, ename, deptno, sal from
(select empno, ename, deptno, sal, avg(sal) over (partition by deptno) avgsal from emp)
where sal>(avgsal+500);
--1)求出工资比我当前员工高出500以外的人员个数
select empno, ename, deptno, sal,
(select count(*) from scott.emp emp1 where emp1.sal>(emp.sal+500) and emp1.deptno=emp.deptno)
from scott.emp;个人认为同部门比较才有意义吧--2)求出每个部门工资高于本部门工资品均值的员工
select * from scott.emp e,(select deptno,avg(sal) dsal from scott.emp group by deptno) d where sal>dsal and e.deptno=d.deptno;