Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 иерархический запрос по 2-м таблицам  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 997
Помогите, пожалуйста, написать иерархический запрос.

Условия:

имеется таблица сотрудников EMP: ID, NAME
имеется таблица отделов, в которых работают сотрудники DEP: ID, NAME

Пусть:

E1, E2, E3, ... - сотрудники
D1, D2, D3, ... - отделы

Имеем следующую картину:

E1: D1 - сотрудник E1 работает в отделе D1
E2: D1,D2 - сотрудник E2 совмещает работу в отделах D1 и D2
E3: D2,D3 - сотрудник E3 совмещает работу в отделах D2 и D3
E4: D3,D4 - сотрудник E4 совмещает работу в отделах D3 и D4
E5: D3,D5,D6 - сотрудник E5 совмещает работу в отделах D3, D5 и D6
E6: D5 - сотрудник E6 работает в отделе D5
E7: D7 - сотрудник E1 работает в отделе D7
...

Необходимо задав сотрудника, получить список всех сотрудников связанных с ним и друг с другом по отделам,
т.е. например по E1 получить E1,E2,E3,E4,E5,E6
по любому из (E1..E6) надо получать E1,E2,E3,E4,E5,E6
а вот по E7 нужно только E7

Сообщение было отредактировано: 29 июн 20, 17:55
29 июн 20, 17:53    [22159193]     Ответить | Цитировать Сообщить модератору
 Re: иерархический запрос по 2-м таблицам  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 997
наверное как то нужно с CONNECT BY PRIOR START WITH

Сообщение было отредактировано: 29 июн 20, 17:54
29 июн 20, 17:55    [22159194]     Ответить | Цитировать Сообщить модератору
 Re: иерархический запрос по 2-м таблицам  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9893
verter,

Данные надо нормализовать а не хранить ввиде CSV. Тут рекурсия:

WITH NORMALIZED AS (
                    SELECT  EMPLOYEE,
                            REGEXP_SUBSTR(DEPARTMENT_LIST,'[^,]+',1,LVL) DEPARTMENT
                      FROM  EMP_TBL,
                            LATERAL(
                                    SELECT  LEVEL LVL
                                      FROM  DUAL
                                      CONNECT BY LEVEL <= REGEXP_COUNT(DEPARTMENT_LIST,'[^,]+')
                                   )
                  ),
R(
  EMPLOYEE,
  DEPARTMENT
 ) AS (
        SELECT  *
          FROM  NORMALIZED
          WHERE EMPLOYEE = '&EMPLOYEE'
       UNION ALL
        SELECT  N2.EMPLOYEE,
                N2.DEPARTMENT
          FROM  NORMALIZED N1,
                NORMALIZED N2,
                R
          WHERE N1.DEPARTMENT = R.DEPARTMENT
            AND N1.EMPLOYEE != R.EMPLOYEE
            AND N2.EMPLOYEE = N1.EMPLOYEE
      )
CYCLE DEPARTMENT SET CYCLE TO 1 DEFAULT 0
SELECT  LISTAGG(DISTINCT EMPLOYEE,',') WITHIN GROUP(ORDER BY EMPLOYEE) EMPLOYEE_LIST
  FROM  R
/


SQL> SELECT  *
  2    FROM  EMP_TBL
  3  /

EM DEPARTME
-- --------
E1 D1
E2 D1,D2
E3 D2,D3
E4 D3,D4
E5 D3,D5,D6
E6 D5
E7 D7

7 rows selected.

SQL> WITH NORMALIZED AS (
  2                      SELECT  EMPLOYEE,
  3                              REGEXP_SUBSTR(DEPARTMENT_LIST,'[^,]+',1,LVL) DEPARTMENT
  4                        FROM  EMP_TBL,
  5                              LATERAL(
  6                                      SELECT  LEVEL LVL
  7                                        FROM  DUAL
  8                                        CONNECT BY LEVEL <= REGEXP_COUNT(DEPARTMENT_LIST,'[^,]+')
  9                                     )
 10                    ),
 11  R(
 12    EMPLOYEE,
 13    DEPARTMENT
 14   ) AS (
 15          SELECT  *
 16            FROM  NORMALIZED
 17            WHERE EMPLOYEE = '&EMPLOYEE'
 18         UNION ALL
 19          SELECT  N2.EMPLOYEE,
 20                  N2.DEPARTMENT
 21            FROM  NORMALIZED N1,
 22                  NORMALIZED N2,
 23                  R
 24            WHERE N1.DEPARTMENT = R.DEPARTMENT
 25              AND N1.EMPLOYEE != R.EMPLOYEE
 26              AND N2.EMPLOYEE = N1.EMPLOYEE
 27        )
 28  CYCLE DEPARTMENT SET CYCLE TO 1 DEFAULT 0
 29  SELECT  LISTAGG(DISTINCT EMPLOYEE,',') WITHIN GROUP(ORDER BY EMPLOYEE) EMPLOYEE_LIST
 30    FROM  R
 31  /
Enter value for employee: E1
old  17:           WHERE EMPLOYEE = '&EMPLOYEE'
new  17:           WHERE EMPLOYEE = 'E1'

EMPLOYEE_LIST
--------------------------------------------------------------------------------
E1,E2,E3,E4,E5,E6

SQL> /
Enter value for employee: E4
old  17:           WHERE EMPLOYEE = '&EMPLOYEE'
new  17:           WHERE EMPLOYEE = 'E4'

EMPLOYEE_LIST
--------------------------------------------------------------------------------
E1,E2,E3,E4,E5,E6

SQL> /
Enter value for employee: E7
old  17:           WHERE EMPLOYEE = '&EMPLOYEE'
new  17:           WHERE EMPLOYEE = 'E7'

EMPLOYEE_LIST
--------------------------------------------------------------------------------
E7

SQL>


SY.
29 июн 20, 22:29    [22159290]     Ответить | Цитировать Сообщить модератору
 Re: иерархический запрос по 2-м таблицам  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8058
verter
Имеем следующую картину:

E1: D1 - сотрудник E1 работает в отделе D1
E2: D1,D2 - сотрудник E2 совмещает работу в отделах D1 и D2
E3: D2,D3 - сотрудник E3 совмещает работу в отделах D2 и D3
E4: D3,D4 - сотрудник E4 совмещает работу в отделах D3 и D4
E5: D3,D5,D6 - сотрудник E5 совмещает работу в отделах D3, D5 и D6
E6: D5 - сотрудник E6 работает в отделе D5
E7: D7 - сотрудник E1 работает в отделе D7
...

Это реализовано в виде таблицы связей многие-ко-многим?
EMPDEPTcomment
E1 D1 сотрудник E1 работает в отделе D1
E2 D1 сотрудник E2 совмещает работу в отделах D1 и D2
E2 D2 сотрудник E2 совмещает работу в отделах D1 и D2
E3 D2 сотрудник E3 совмещает работу в отделах D2 и D3
E3 D3 сотрудник E3 совмещает работу в отделах D2 и D3
E4 D3 сотрудник E4 совмещает работу в отделах D3 и D4
E4 D4 сотрудник E4 совмещает работу в отделах D3 и D4
E5 D3 сотрудник E5 совмещает работу в отделах D3, D5 и D6
E5 D5 сотрудник E5 совмещает работу в отделах D3, D5 и D6
E5 D6 сотрудник E5 совмещает работу в отделах D3, D5 и D6
E6 D5 сотрудник E6 работает в отделе D5
E7 D7 сотрудник E1 работает в отделе D7
29 июн 20, 22:45    [22159299]     Ответить | Цитировать Сообщить модератору
 Re: иерархический запрос по 2-м таблицам  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 997
SQL*Plus

Это реализовано в виде таблицы связей многие-ко-многим?
EMPDEPTcomment
E1 D1 сотрудник E1 работает в отделе D1
E2 D1 сотрудник E2 совмещает работу в отделах D1 и D2
E2 D2 сотрудник E2 совмещает работу в отделах D1 и D2
E3 D2 сотрудник E3 совмещает работу в отделах D2 и D3
E3 D3 сотрудник E3 совмещает работу в отделах D2 и D3
E4 D3 сотрудник E4 совмещает работу в отделах D3 и D4
E4 D4 сотрудник E4 совмещает работу в отделах D3 и D4
E5 D3 сотрудник E5 совмещает работу в отделах D3, D5 и D6
E5 D5 сотрудник E5 совмещает работу в отделах D3, D5 и D6
E5 D6 сотрудник E5 совмещает работу в отделах D3, D5 и D6
E6 D5 сотрудник E6 работает в отделе D5
E7 D7 сотрудник E1 работает в отделе D7


Да, связь многие-ко-многим, т.е. есть таблица связок сотрудников с отделами, назовём её EMP_DEP, она содержит все связки:
emp_id   dep_id
1            1
2            1
2            2
3            2
3            3
4            3
4            4
5            3
5            5
5            6
6            5
7            7


Сообщение было отредактировано: 30 июн 20, 02:18
30 июн 20, 02:17    [22159355]     Ответить | Цитировать Сообщить модератору
 Re: иерархический запрос по 2-м таблицам  [new]
xtender
Member

Откуда: Мск
Сообщений: 5472
verter
Необходимо задав сотрудника, получить список всех сотрудников связанных с ним и друг с другом по отделам,
это лучше через стандартные алгоритмы для connected components делать: http://orasql.org/2017/09/29/connected-components/
На форуме уже было:
Количество груп связей в many to many
30 июн 20, 04:49    [22159368]     Ответить | Цитировать Сообщить модератору
 Re: иерархический запрос по 2-м таблицам  [new]
xtender
Member

Откуда: Мск
Сообщений: 5472
verter
Да, связь многие-ко-многим, т.е. есть таблица связок сотрудников с отделами, назовём её EMP_DEP, она содержит все связки:
Решение для EMP_DEP:
+
create table emp_dep(emp_id,dep_id) as
select 1 ,           1 from dual union all
select 2 ,           1 from dual union all
select 2 ,           2 from dual union all
select 3 ,           2 from dual union all
select 3 ,           3 from dual union all
select 4 ,           3 from dual union all
select 4 ,           4 from dual union all
select 5 ,           3 from dual union all
select 5 ,           5 from dual union all
select 5 ,           6 from dual union all
select 6 ,           5 from dual union all
select 7 ,           7 from dual 
/    
create type numbers_table as table of number
/
declare
   type int_array    is table of pls_integer index by pls_integer;
   type arr_elems    is table of /*sys.ku$_objnumset*/ numbers_table index by pls_integer;
   root              int_array;
   root_elems        arr_elems;
 
   n        int;
   clients  int_array;
   accounts int_array;
 
    l integer:=dbms_utility.get_time();
 
    procedure print(v in varchar2) is
    begin
      dbms_output.put_line(to_char((dbms_utility.get_time-l)/100,'0999.99')||' '||v);
      l:=dbms_utility.get_time();
    end;
 
    
   function get_root(n int) return pls_integer is
   begin
      if root.exists(n) then
         return root(n);
      else
         return null;
      end if;
   end;
    
   procedure update_root(old_root pls_integer,new_root pls_integer) is
      i       pls_integer;
      elem    pls_integer;
      cnt_old pls_integer;
      cnt_new pls_integer;
   begin
      if old_root!=new_root then
         --root_elems(new_root):=root_elems(new_root) multiset union all root_elems(old_root);
         cnt_old:=root_elems(old_root).count;
         cnt_new:=root_elems(new_root).count;
         root_elems(new_root).extend(cnt_old);
         for i in 1..cnt_old
         loop
            elem := root_elems(old_root)(i);
            root(elem):=new_root;
            root_elems(new_root)(cnt_new+i):=elem;
         end loop;
         root_elems(old_root).delete;
      end if;
   end;
    
   procedure add_elem(p_root pls_integer, p_elem pls_integer) is
   begin
      if not root_elems.exists(p_root) then
         root_elems(p_root):=numbers_table(p_elem); --sys.ku$_objnumset(p_elem);
      else
         root_elems(p_root).extend();
         root_elems(p_root)(root_elems(p_root).count):=p_elem;
      end if;
   end;
    
   procedure add_link(clientid pls_integer,accountid pls_integer) is
      r1       pls_integer;
      r2       pls_integer;
      new_root pls_integer;
   begin
      r1:=get_root(clientid);
      r2:=get_root(accountid);
       
      if r1 is null or r2 is null then
         new_root := coalesce(r1,r2,clientid);
         if r1 is null then add_elem(new_root,clientid ); root(clientid) :=new_root; end if;
         if r2 is null then add_elem(new_root,accountid); root(accountid):=new_root; end if;
      else
         new_root := least(r1,r2);
         root(clientid) :=new_root;
         root(accountid):=new_root;
         update_root(greatest(r1,r2),new_root);
      end if;
   end;
    
   function str_format(p int) return varchar2 is
   begin
      return utl_lms.format_message('(%d, %d) = group #%d'
                                   ,clients(p)
                                   ,accounts(p)
                                   ,get_root(clients(p))
                                   );
   end;
begin
   print('start');
  -- твой запрос тут:
   select dep_id,emp_id
          bulk collect into clients,accounts
   from EMP_DEP;
   print('fetched');
   n:=clients.count;
   dbms_output.put_line('count='||n);
   for i in 1..n loop
      add_link(clients(i),accounts(i));
   end loop;
   print('processed');
---
--/* -- тут можно вывести все связи, если нужно
   for i in 1..n loop
      dbms_output.put_line(str_format(i));
   end loop;
--   */
--/* -- либо как у тебя в задании вывести только связанных - подставь X
   dbms_output.put_line('connected to &X:');
   for i in 1..root_elems(get_root(&X)).count loop
      dbms_output.put_line(root_elems(get_root(&X))(i));
   end loop;
--*/
end;
30 июн 20, 05:10    [22159373]     Ответить | Цитировать Сообщить модератору
 Re: иерархический запрос по 2-м таблицам  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 997
SY,

благодарю за ответ, именно таким образом я и представлял себе, что нужно как то с WITH и регулярными выражениями.

я не совсем точно поставил задачу, на самом деле условия задачи такие:

есть таблица EMP (e_id integer)
e_id
1
2
3
4
5
6
7

есть таблица DEP (d_id integer)
d_id
1
2
3
4
5
6
7

есть таблица-связок многих ко многим EMP_DEP (emp_id integer, dep_id integer)
emp_id   dep_id
1            1
2            1
2            2
3            2
3            3
4            3
4            4
5            3
5            5
5            6
6            6
7            7

Необходимо на вход подать id сотрудника, на выходе должен быть датасет из id острудников, связанных по отделам, т.е. так:
emp_id=1
emp_id
1
2
3
4
5
6

emp_id=2
emp_id
1
2
3
4
5
6

emp_id=7
emp_id
7

Я к тому что, никаких строковых литералов не требуется, только IDs.

Подскажите, пожалуйста, как тогда переписать ваше решение:
WITH NORMALIZED AS (
                    SELECT  EMPLOYEE,
                            REGEXP_SUBSTR(DEPARTMENT_LIST,'[^,]+',1,LVL) DEPARTMENT
                      FROM  EMP_TBL,
                            LATERAL(
                                    SELECT  LEVEL LVL
                                      FROM  DUAL
                                      CONNECT BY LEVEL <= REGEXP_COUNT(DEPARTMENT_LIST,'[^,]+')
                                   )
                  ),
R(
  EMPLOYEE,
  DEPARTMENT
 ) AS (
        SELECT  *
          FROM  NORMALIZED
          WHERE EMPLOYEE = '&EMPLOYEE'
       UNION ALL
        SELECT  N2.EMPLOYEE,
                N2.DEPARTMENT
          FROM  NORMALIZED N1,
                NORMALIZED N2,
                R
          WHERE N1.DEPARTMENT = R.DEPARTMENT
            AND N1.EMPLOYEE != R.EMPLOYEE
            AND N2.EMPLOYEE = N1.EMPLOYEE
      )
CYCLE DEPARTMENT SET CYCLE TO 1 DEFAULT 0
SELECT  LISTAGG(DISTINCT EMPLOYEE,',') WITHIN GROUP(ORDER BY EMPLOYEE) EMPLOYEE_LIST
FROM  R
30 июн 20, 12:51    [22159608]     Ответить | Цитировать Сообщить модератору
 Re: иерархический запрос по 2-м таблицам  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9893
А сразу CREATE TABLE дать не мог? Просто убери NORMALIZED из моего решения:

WITH R(
       EMP_ID,
       DEP_ID
      ) AS (
             SELECT  EMP_ID,
                     DEP_ID
               FROM  EMP_DEP
               WHERE EMP_ID = '&EMP_ID'
            UNION ALL
             SELECT  X2.EMP_ID,
                     X2.DEP_ID
               FROM  EMP_DEP X1,
                     EMP_DEP X2,
                     R
               WHERE X1.DEP_ID = R.DEP_ID
                 AND X1.EMP_ID != R.EMP_ID
                 AND X2.EMP_ID = X1.EMP_ID
           )
CYCLE DEP_ID SET CYCLE TO 1 DEFAULT 0
SELECT  DISTINCT EMP_ID
  FROM  R
  ORDER BY EMP_ID
/
Enter value for emp_id: 1
old   8:                WHERE EMP_ID = '&EMP_ID'
new   8:                WHERE EMP_ID = '1'

    EMP_ID
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

SQL> /
Enter value for emp_id: 4
old   8:                WHERE EMP_ID = '&EMP_ID'
new   8:                WHERE EMP_ID = '4'

    EMP_ID
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

SQL> /
Enter value for emp_id: 7
old   8:                WHERE EMP_ID = '&EMP_ID'
new   8:                WHERE EMP_ID = '7'

    EMP_ID
----------
         7

SQL>


SY.
30 июн 20, 12:57    [22159613]     Ответить | Цитировать Сообщить модератору
 Re: иерархический запрос по 2-м таблицам  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 997
SY,

прошу прощения, всегда сложности из-за неточной постановки задачи

Огромное спасибо за это красивое решение!
30 июн 20, 12:59    [22159617]     Ответить | Цитировать Сообщить модератору
 Re: иерархический запрос по 2-м таблицам  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8058
verter
прошу прощения, всегда сложности из-за неточной постановки задачи

Точная постановка задачи - это уже половина решения.
Причем в половине случаев автор сам находит решение в процессе формулирования точной постановки задачи! :-)
30 июн 20, 13:05    [22159623]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить