Oracle SQL >>同一表中父项、子项和孙项的分层查询

Question

我在表中有数据，如下面的结构，

Manager Id  Employee id chartfield
SM1           MGR-1        12
SM2           MGR-1        12
MGR-1         LEAD-1       12
MGR-1         LEAD-2       12
MGR-1         LEAD-3       12
LEAD-1        LEAD-2       12
LEAD-1      ASSOCIATE -1    12
LEAD-1      ASSOCIATE -2    12
LEAD-2       LEAD-3         12
LEAD-2      ASSOCIATE -3    12
LEAD-2      ASSOCIATE -4    12
LEAD-3      ASSOCIATE -5    12
LEAD-3      ASSOCIATE -6    12
ASSOCIATE -1    JUNIOR - 1  12
ASSOCIATE -1    JUNIOR - 2  12
ASSOCIATE -2    JUNIOR - 1  12
ASSOCIATE -2    JUNIOR - 2  12

我期望有经理和员工的产出，并以他们所处的水平。这里唯一的标准是同一名员工可以向多个经理报告，此案我们应该只选择向最低级别向该员工报告的不同员工。在上述情况下，Lead-2向MGR -1和Lead-1报告，但我们正在考虑最低水平。

样本输出可能就像以下结构，

Manager Id  Employee id chartfield  LEVEL1
MGR-1   LEAD-1           12           1
MGR-1   LEAD-2           12           1
MGR-1   LEAD-3           12           1
MGR-1   ASSOCIATE -1    12            2
MGR-1   ASSOCIATE -2    12            2
MGR-1   ASSOCIATE -3    12            2
MGR-1   ASSOCIATE -4    12            2
MGR-1   ASSOCIATE -5    12            2
MGR-1   ASSOCIATE -6    12            2
MGR-1   JUNIOR - 1      12            3
MGR-1   JUNIOR - 2      12            3

Answer 1

您的示例数据显示每个人都向SM1和SM2报告.../p>

WITH t (
  MANAGER_ID,
  EMPLOYEE_ID,
  LEVEL_NUM
) AS (
  SELECT
    e.MANAGER_ID,
    e.MANAGER_ID,
    0 AS LEVEL_NUM
  FROM
    emp e
  WHERE
    e.MANAGER_ID NOT IN (
      SELECT
        e.EMPLOYEE_ID
      FROM
        emp e
      )
  UNION ALL
    SELECT
      t.MANAGER_ID,
      e.EMPLOYEE_ID,
      t.LEVEL_NUM + 1 AS LEVEL_NUM      
    FROM
      emp e
    JOIN
      t
    ON
      e.MANAGER_ID = t.EMPLOYEE_ID
)
SELECT
  MANAGER_ID,
  EMPLOYEE_ID,
  LEVEL_NUM
FROM
  (
    SELECT
      MANAGER_ID,
      EMPLOYEE_ID,
      LEVEL_NUM,
      ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID ORDER BY LEVEL_NUM, MANAGER_ID) AS ROW_NUM
    FROM
      t
  ) t
WHERE
  t.ROW_NUM = 1 -- keep only the first manager
AND
  t.LEVEL_NUM > 0  -- exclude top level managers reporting to themselves
ORDER BY
  t.LEVEL_NUM,
  t.MANAGER_ID,
  t.EMPLOYEE_ID;

以及处理数据中周期的查询：

WITH t (
  MANAGER_ID,
  EMPLOYEE_ID,
  PATH,
  LEVEL_NUM
) AS (
  SELECT
    e.MANAGER_ID,
    e.MANAGER_ID,
    '> ' || e.MANAGER_ID AS PATH,
    0 AS LEVEL_NUM
  FROM
    emp e
  UNION ALL
    SELECT
      t.MANAGER_ID,
      e.EMPLOYEE_ID,
      t.PATH || ' > ' || e.EMPLOYEE_ID AS PATH,
      t.LEVEL_NUM + 1 AS LEVEL_NUM
    FROM
      emp e
    JOIN
      t
    ON
      e.MANAGER_ID = t.EMPLOYEE_ID
    WHERE
     t.PATH NOT LIKE '%> ' || e.EMPLOYEE_ID || '%'
)
SELECT
  t.MANAGER_ID,
  t.EMPLOYEE_ID,
  t.LEVEL_NUM
FROM
  (
    SELECT
      t.MANAGER_ID,
      t.EMPLOYEE_ID,
      t.LEVEL_NUM,
      ROW_NUMBER() OVER (PARTITION BY t.MANAGER_ID, t.EMPLOYEE_ID ORDER BY t.LEVEL_NUM) AS ROW_NUM
    FROM
      t
  ) t
WHERE
  t.ROW_NUM = 1 -- keep only the first employee occurance
AND
  t.LEVEL_NUM > 0  -- exclude top level managers reporting to themselves
AND
  t.MANAGER_ID = 'SM1'  -- selected manager
ORDER BY
  t.LEVEL_NUM,
  t.MANAGER_ID,
  t.EMPLOYEE_ID;