Oracle求员工工资

Question

有1个员工表3个字段：emp_id, dept_id, salary（工资）。
求低于员工所在部门平均工资500的员工有哪一些。SQL要求：只能对该表扫描一次。

Answer 1

select emp_id, hiredate, sal, dept_id, avg_sal
from (
      select e.emp_id, e.salary, e.dept_id,
             avg(e.salary) over( partition by e.dept_id ) as avg_sal
      from emp e
     ) t
where t.avg_sal-sal>500;

Answer 2

select emp_id, hiredate, salary, dept_id, avg_sal
from (
      select e.emp_id, e.salary, e.dept_id,
             avg(e.salary) over( partition by e.dept_id ) as avg_sal
      from emp e
     ) t
where t.avg_sal-sal>500;

Answer 3

     avg(e.salary) over( partition by e.dept_id ) as avg_sal还有这么一个分析函数啊

Answer 4

select a.emp_id,a.dept_id,a.salary
from emp a,(select dept_id,avg(salary) avg_salary
from emp group by dept_id having avg(salary)=500) b
where a.dept_id=b.dept_id and a.salary<=avg_salary
--使用avg() over()
select b.emp_id,b.dept_id,b.salary
from (select dept_id,salary,avg(salary) over(partition by dept_id) avg_salary
from emp) a,emp b
where avg_salary=500 and  a.dept_id=b.dept_id and a.salary<=avg_salary

Answer 5

--类似还有
max(salary) over( partition by dept_id ) 
-- 部门最高工资
min(salary) over( partition by dept_id ) 
-- 部门最高工资
sum(salary) over( partition by dept_id ) 
-- 部门工资总数

Answer 6

sql写是可以这样写 但不符合要求啊 对这张表不止扫描一次了吧

Answer 7

scott@SZTYORA> select empno, hiredate, sal, deptno, avg_sal
  2  from (
  3        select e.empno, e.hiredate, e.sal, e.deptno,
  4               avg(e.sal) over( partition by e.deptno ) as avg_sal
  5        from emp e
  6       ) t
  7  where t.avg_sal-sal>500;     EMPNO HIREDATE            SAL     DEPTNO    AVG_SAL
---------- ------------ ---------- ---------- ----------
      7782 09-JUN-81          2695         10       3365
      7934 23-JAN-82          2400         10       3365
      7499 20-FEB-81          1760         30 92764.3182
      7902 03-DEC-81          3000         30 92764.3182
      7654 28-SEP-81          1250         30 92764.3182
      7844 08-SEP-81          1500         30 92764.3182
      7900 03-DEC-81           950         30 92764.3182
      7788 19-APR-87          3000         30 92764.3182
      7566 02-APR-81        3272.5         30 92764.3182
      7521 22-FEB-81          1375         30 92764.3182
      7876 23-MAY-87          1100         30 92764.3182
      7698 01-MAY-81          3200         30 92764.3182已选择12行。已用时间:  00: 00: 00.12执行计划
----------------------------------------------------------
Plan hash value: 4130734685----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   854 |     3  (34)| 00:00:01 |
|*  1 |  VIEW               |      |    14 |   854 |     3  (34)| 00:00:01 |
|   2 |   WINDOW SORT       |      |    14 |   266 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   266 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("T"."AVG_SAL"-"SAL">500)
统计信息
----------------------------------------------------------
        127  recursive calls
          0  db block gets
         22  consistent gets
         10  physical reads
          0  redo size
        948  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         12  rows processedscott@SZTYORA>