时间戳

首页 > 解决方案 > 根据拥有多少员工对经理进行排名

oracle - 根据拥有多少员工对经理进行排名

问题描述

[更新]

感谢所有的评论！赞赏！

在参考了您的所有帖子后，我通过下面的代码解决了这个问题。我没有考虑等级，但我会考虑以后的作品。谢谢！

SELECT m.first_name
  , m.last_name
  , RANK() over (partition by (select COUNT(e.employee_id) 
                               from DB1_employee e) 
                 ORDER BY e.employee_id DESC) AS RANK from DB2_manager m LEFT JOIN RITDB_employee e ON m.employee_id=e.manager

我很难对我的代码进行故障排除，以便根据他们拥有的员工数量对经理进行排名。错误是“缺少右括号”。任何帮助将不胜感激。谢谢！

SELECT m.first_name
      , m.last_name
      , RANK() over (partition by (select COUNT(e.employee_id) 
                                   from DB1_employee e) AS NUM_EMP 
                     ORDER BY NUM_EMP DESC) AS RANK 
from DB2_manager m
     , DB1_employee e 
group by m.first_name, m.last_name 
ORDER BY RANK

标签： oraclegreatest-n-per-group

解决方案

一个基于 Scott 架构的示例（因为我没有您的表和数据），以便更容易查看应该返回的内容。

SQL> select mgr, empno, ename, job from emp order by mgr;

       MGR      EMPNO ENAME      JOB
---------- ---------- ---------- ---------
      7566       7902 FORD       ANALYST        --> MGR 7566 (Jones)  has 2 employees
      7566       7788 SCOTT      ANALYST
      7698       7900 JAMES      CLERK          -->     7698 (Blake)  has 5 employees
      7698       7499 ALLEN      SALESMAN
      7698       7521 WARD       SALESMAN
      7698       7844 TURNER     SALESMAN
      7698       7654 MARTIN     SALESMAN
      7782       7934 MILLER     CLERK          -->     7782 (Clark)  has 1 employee
      7788       7876 ADAMS      CLERK          -->     7788 (Scott)  has 1 employee
      7839       7698 BLAKE      MANAGER        -->     7839 (King)   has 3 employees
      7839       7566 JONES      MANAGER
      7839       7782 CLARK      MANAGER
      7902       7369 SMITH      CLERK          -->     7902 (Ford)   has 1 employee 
                 7839 KING       PRESIDENT

14 rows selected.

SQL>

所以：

SQL> select m.ename mgrname,
  2         count(*) cnt,
  3         dense_rank() over (order by count(*) desc) rnk
  4  from emp e join emp m on e.mgr = m.empno
  5  where e.mgr is not null
  6  group by e.mgr, m.ename
  7  order by rnk;

MGRNAME           CNT        RNK
---------- ---------- ----------
BLAKE               5          1
KING                3          2
JONES               2          3
SCOTT               1          4
FORD                1          4
CLARK               1          4

6 rows selected.

SQL>

然而：

这是不正确的。为什么？因为等级制度。并非所有经理都共享同一级别。看看：国王是总统；他是大老板，管理着他们；他怎么能排在#2？其他人也是如此。

SQL> select lpad(' ', 2 * level - 2) || e.empno ||' '|| e.ename val
  2  from emp e
  3  connect by prior e.empno = e.mgr
  4  start with e.mgr is null;

VAL
--------------------------------------------------------------------
7839 KING
  7566 JONES               --> Jones has 4 employees!!! (Scott, Adams, Ford and Smith)
    7788 SCOTT                 --> Scott has 1 employee (that's Adams)
      7876 ADAMS
    7902 FORD                  --> Ford  has 1 employee (that's Smith)
      7369 SMITH
  7698 BLAKE
    7499 ALLEN
    7521 WARD
    7654 MARTIN
    7844 TURNER
    7900 JAMES
  7782 CLARK
    7934 MILLER

14 rows selected.

SQL>

因此，我们需要另一种方法。从一个简单的查询开始，基本上，它为每个查询返回“root”：

SQL> select connect_by_root(ename) manager
  2  from emp
  3  connect by prior empno = mgr;

MANAGER
----------
SCOTT
SCOTT
FORD
FORD
ALLEN
JAMES

<snip>

KING
KING

39 rows selected.

SQL>

它被进一步用作源

SQL> select x.mgrname,
  2         count(*) - 1 cnt
  3  from (select connect_by_root(e.ename) mgrname
  4        from emp e
  5        connect by prior e.empno = e.mgr
  6       ) x
  7  group by x.mgrname;

MGRNAME           CNT
---------- ----------
ALLEN               0
JONES               4
FORD                1
MILLER              0
CLARK               1
WARD                0
SMITH               0
SCOTT               1
TURNER              0
MARTIN              0
ADAMS               0
JAMES               0
BLAKE               5
KING               13

14 rows selected.

SQL>

最后，删除那些没有员工的并对其进行排名；结果与第一种（最明显，但可能是错误的）方法完全不同：

SQL> select r.mgrname,
  2         r.cnt,
  3         dense_rank() over (order by cnt desc) rnk
  4  from (select x.mgrname,
  5               count(*) - 1 cnt
  6        from (select connect_by_root(e.ename) mgrname
  7              from emp e
  8              connect by prior e.empno = e.mgr
  9             ) x
 10        group by x.mgrname
 11       ) r
 12  where r.cnt > 0
 13  order by rnk;

MGRNAME           CNT        RNK
---------- ---------- ----------
KING               13          1
BLAKE               5          2
JONES               4          3
SCOTT               1          4
FORD                1          4
CLARK               1          4

6 rows selected.

SQL>

推荐阅读