如何计算每个部门的员工

DaviRoli 发表于 Dev

达维罗利

希望你们能提供一些见识哦，如何修改我的查询。

需要的结果：仅显示部门中有2名以上员工的员工。-----------这意味着我的结果应该仅给出7行（部门销售和IT部门的3和4）

任何帮助是极大的赞赏。

我有这个查询：

SELECT b.employee_id,b.salary, a.department_id, a.department_name,
  max(b.salary) over (partition by a.department_id) as max_sal
FROM        department a, employee b
WHERE       a.department_id(+) = b.department_id

这些是我目前的结果：

EMPLOYEE_ID     SALARY DEPARTMENT_ID DEPARTMENT_NAME         MAX_SAL
----------- ---------- ------------- -------------------- ----------
       7566       3000            10 ACCOUNTING                 3000 
       7999       2500            20 RESEARCH                   3000 
       7610       3000            20 RESEARCH                   3000 
       7921       2500            30 SALES                      3000 
       7952       2000            30 SALES                      3000 
       7900       3000            30 SALES                      3000 
       7934       1000            40 IT                         2900 
       7876       2000            40 IT                         2900 
       7788       2500            40 IT                         2900 
       7910       2900            40 IT                         2900 
       7603       4000            50 EXECUTIVE                  5000 
       7596       4500            50 EXECUTIVE                  5000 
       8000       2500                                          2500

13 rows selected

阿尔宾·孙南波

SELECT b.employee_id,b.salary, a.department_id, a.department_name,
  max(b.salary) over (partition by a.department_id) as max_sal
FROM        department a, employee b
WHERE       a.department_id(+) = b.department_id
AND         a.department_id IN (
    SELECT department_id from employee
    GROUP BY department_id
    HAVING COUNT(employee_id) > 2
)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。