时间戳

首页 > 解决方案 > 从提及员工和主管的表中获取从属值列表

oracle - 从提及员工和主管的表中获取从属值列表

问题描述

我有一个如下数据集

员工 导师
一种 乙
乙 C
D 乙
F 乙
G F

在这个数据集中，当我给主管作为输入时，我想一个一个地访问每个员工。例如，如果我将 B 作为输入，首先获取 A 并像这样去 B 直到最后，然后返回到 F，然后转到 G。我想按顺序获取它们，以便我必须应用一些数据整个数据集的最高主管。先感谢您。

标签： oracleplsqlhierarchical-data

解决方案

这就是我理解这个问题的方式；第一列 ( employee) 显示了主管下属的所有员工（c在本例中），同时path显示......好吧，从所选主管到该员工的路径。

SQL> with dataset (employee, supervisor) as
  2    (select 'a', 'b' from dual union all
  3     select 'b', 'c' from dual union all
  4     select 'd', 'e' from dual union all
  5     select 'f', 'b' from dual union all
  6     select 'g', 'f' from dual
  7    )
  8  select
  9    employee,
 10    supervisor,
 11    ltrim(sys_connect_by_path(employee, ' - '), ' - ') as path
 12  from dataset
 13  start with supervisor = 'c'                 --> this is the supervisor you're interested in
 14  connect by supervisor = prior employee;

E S PATH
- - --------------------
b c b
a b b - a
f b b - f
g f b - f - g

SQL>

如果您想通过员工“循环”，那么您将需要 PL/SQL 和......好吧，一个循环。像这样的东西：

SQL> set serveroutput on
SQL> declare
  2    l_supervisor varchar2(1) := 'c';
  3  begin
  4    for cur_r in
  5      (with dataset (employee, supervisor) as
  6          (select 'a', 'b' from dual union all
  7           select 'b', 'c' from dual union all
  8           select 'd', 'e' from dual union all
  9           select 'f', 'b' from dual union all
 10           select 'g', 'f' from dual
 11          )
 12        select
 13          employee,
 14          supervisor,
 15          ltrim(sys_connect_by_path(employee, ' - '), ' - ') as path
 16        from dataset
 17        start with supervisor = l_supervisor
 18        connect by supervisor = prior employee
 19      )
 20    loop
 21      -- you'd do something with this employee; I'm just displaying it
 22      dbms_output.put_line('Employee = ' || cur_r.employee);
 23    end loop;
 24  end;
 25  /
Employee = b
Employee = a
Employee = f
Employee = g

PL/SQL procedure successfully completed.

SQL>

推荐阅读