ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / иерархический запрос по 2-м таблицам
11 сообщений из 11, страница 1 из 1
иерархический запрос по 2-м таблицам
    #39974248
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
verter
Участник
Помогите, пожалуйста, написать иерархический запрос.

Условия:

имеется таблица сотрудников 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
...
Рейтинг: 0 / 0
29.06.2020, 17:53
| Ответить | Цитировать | Написать  
иерархический запрос по 2-м таблицам
    #39974249
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
verter
Участник
наверное как то нужно с CONNECT BY PRIOR START WITH
...
Рейтинг: 0 / 0
29.06.2020, 17:55
| Ответить | Цитировать | Написать  
иерархический запрос по 2-м таблицам
    #39974300
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
verter,

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

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
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
/



Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
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.
...
Рейтинг: 0 / 0
29.06.2020, 22:29
| Ответить | Цитировать | Написать  
иерархический запрос по 2-м таблицам
    #39974306
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
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
...

Это реализовано в виде таблицы связей многие-ко-многим?
EMPDEPTcommentE1 D1 сотрудник E1 работает в отделе D1E2 D1 сотрудник E2 совмещает работу в отделах D1 и D2E2 D2 сотрудник E2 совмещает работу в отделах D1 и D2E3 D2 сотрудник E3 совмещает работу в отделах D2 и D3E3 D3 сотрудник E3 совмещает работу в отделах D2 и D3E4 D3 сотрудник E4 совмещает работу в отделах D3 и D4E4 D4 сотрудник E4 совмещает работу в отделах D3 и D4E5 D3 сотрудник E5 совмещает работу в отделах D3, D5 и D6E5 D5 сотрудник E5 совмещает работу в отделах D3, D5 и D6E5 D6 сотрудник E5 совмещает работу в отделах D3, D5 и D6E6 D5 сотрудник E6 работает в отделе D5E7 D7 сотрудник E1 работает в отделе D7
...
Рейтинг: 0 / 0
29.06.2020, 22:45
| Ответить | Цитировать | Написать  
иерархический запрос по 2-м таблицам
    #39974346
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
verter
Участник
SQL*Plus

Это реализовано в виде таблицы связей многие-ко-многим?
EMPDEPTcommentE1 D1 сотрудник E1 работает в отделе D1E2 D1 сотрудник E2 совмещает работу в отделах D1 и D2E2 D2 сотрудник E2 совмещает работу в отделах D1 и D2E3 D2 сотрудник E3 совмещает работу в отделах D2 и D3E3 D3 сотрудник E3 совмещает работу в отделах D2 и D3E4 D3 сотрудник E4 совмещает работу в отделах D3 и D4E4 D4 сотрудник E4 совмещает работу в отделах D3 и D4E5 D3 сотрудник E5 совмещает работу в отделах D3, D5 и D6E5 D5 сотрудник E5 совмещает работу в отделах D3, D5 и D6E5 D6 сотрудник E5 совмещает работу в отделах D3, D5 и D6E6 D5 сотрудник E6 работает в отделе D5E7 D7 сотрудник E1 работает в отделе D7


Да, связь многие-ко-многим, т.е. есть таблица связок сотрудников с отделами, назовём её EMP_DEP, она содержит все связки:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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
...
Рейтинг: 0 / 0
30.06.2020, 02:17
| Ответить | Цитировать | Написать  
иерархический запрос по 2-м таблицам
    #39974357
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
verter
Необходимо задав сотрудника, получить список всех сотрудников связанных с ним и друг с другом по отделам,
это лучше через стандартные алгоритмы для connected components делать: http://orasql.org/2017/09/29/connected-components/
На форуме уже было:
Количество груп связей в many to many
...
Рейтинг: 0 / 0
30.06.2020, 04:49
| Ответить | Цитировать | Написать  
иерархический запрос по 2-м таблицам
    #39974361
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
verter
Да, связь многие-ко-многим, т.е. есть таблица связок сотрудников с отделами, назовём её EMP_DEP, она содержит все связки:
Решение для EMP_DEP:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
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;

...
Рейтинг: 0 / 0
30.06.2020, 05:10
| Ответить | Цитировать | Написать  
иерархический запрос по 2-м таблицам
    #39974526
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
verter
Участник
SY,

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

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

есть таблица EMP (e_id integer)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
e_id
1
2
3
4
5
6
7


есть таблица DEP (d_id integer)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
d_id
1
2
3
4
5
6
7


есть таблица-связок многих ко многим EMP_DEP (emp_id integer, dep_id integer)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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 острудников, связанных по отделам, т.е. так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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.

Подскажите, пожалуйста, как тогда переписать ваше решение:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
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
...
Рейтинг: 0 / 0
30.06.2020, 12:51
| Ответить | Цитировать | Написать  
иерархический запрос по 2-м таблицам
    #39974529
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
А сразу CREATE TABLE дать не мог? Просто убери NORMALIZED из моего решения:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
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.
...
Рейтинг: 0 / 0
30.06.2020, 12:57
| Ответить | Цитировать | Написать  
иерархический запрос по 2-м таблицам
    #39974532
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
verter
Участник
SY,

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

Огромное спасибо за это красивое решение!
...
Рейтинг: 0 / 0
30.06.2020, 12:59
| Ответить | Цитировать | Написать  
иерархический запрос по 2-м таблицам
    #39974537
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
verter
прошу прощения, всегда сложности из-за неточной постановки задачи

Точная постановка задачи - это уже половина решения.
Причем в половине случаев автор сам находит решение в процессе формулирования точной постановки задачи! :-)
...
Рейтинг: 0 / 0
30.06.2020, 13:05
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / иерархический запрос по 2-м таблицам
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39974357&tid=1881108]:
 0ms
get settings:
 7ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 57ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 44ms
get tp. blocked users:
 1ms
others: 310ms
total:  449ms
созд. / загр.: 449ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]