Объединение Oracle с рекурсивным

#sql #oracle #recursion #join

#sql #Oracle #рекурсия #Присоединиться

Вопрос:

У меня есть таблица employee и таблица department.

Я хочу написать запрос, в котором перечислены идентификаторы employee_ids, если в столбце статуса его dep_id есть ‘A’.

Например:

для employee_id = 1 я должен видеть 1, потому что его родительский dept_id(10) имеет статус = ‘A’

для employee_id = 2 я должен видеть 2, потому что его родительский dept_id(200) имеет статус = ‘A’

для employee_id = 5 я не должен использовать 5, потому что его родители не имеют status = ‘A’

 Department Table

employee_id | dept_id     | status |level
1           |10           | null   | 1
2           |20           | null   | 1
10          |100          | A      | 2
20          |200          | null   | 2
100         |1000         | null   | 3
200         |2000         | A      | 3
450         |750          | null   | 1
5           |30           | null   | 1
30          |300          | null   | 2
3           |400          | A      | 1
400         |4000         | null   | 2

Employee Table

employee_id | employee_name
1           |  steven
2           |  jone
3           |  eric
4           |  mark
5           |  harry

Пример результата для двух приведенных выше таблиц :

   | employee_id|
      1
      2

Теперь мой запрос такой :

   SELECT Employee.employee_id FROM Department
  JOIN Employee ON Employee.employee_id = Department.employee_id;

1. AFAIK, это просто добавление WHERE Department.status = 'A'

2. оно также дает 3, но его родительский отдел не содержит «A». Критерий — родительский отдел должен содержать «A». Запрос должен проверять родительские подразделения

3. Ах, извините, не понял рекурсивного соединения через dept_id = employee_id, как это сделал @Tejash

Ответ №1:

Вам нужен иерархический запрос следующим образом:

 SQL> -- SAMPLE DATA
SQL> WITH DEPT_EMP (employee_id , dept_id, status, lVL) as
  2  (select 1   , 10  ,  null, 1 from dual union all
  3  select 2   , 20  ,  null, 1 from dual union all
  4  select 10  , 100 ,  'A'   , 2 from dual union all
  5  select 20  , 200 ,  null, 2 from dual union all
  6  select 100 , 1000,  null, 3 from dual union all
  7  select 200 , 2000, 'A'   , 3 from dual union all
  8  select 450 , 750 ,  null, 1 from dual union all
  9  select 5   , 30  ,  null, 1 from dual union all
 10  select 30  , 300 ,  null, 2 from dual union all
 11  select 3   , 400 ,  'A'   , 1 from dual union all
 12  select 400 , 4000,  null, 2 from dual),
 13  EMPS (employee_id, employee_name) AS
 14  (SELECT 1, 'steven' FROM DUAL UNION ALL
 15  SELECT 2, 'jone'   FROM DUAL UNION ALL
 16  SELECT 3, 'eric'   FROM DUAL UNION ALL
 17  SELECT 4, 'mark'   FROM DUAL UNION ALL
 18  SELECT 5, 'harry'  FROM DUAL)
 19  -- YOUR QUERY STARTS FROM HERE
 20  SELECT D.EMPLOYEE_ID, E.EMPLOYEE_NAME
 21    FROM (SELECT STATUS, LVL, CONNECT_BY_ROOT EMPLOYEE_ID AS EMPLOYEE_ID
 22    FROM DEPT_EMP
 23  CONNECT BY  EMPLOYEE_id = PRIOR dept_id) D
 24    JOIN EMPS E ON D.EMPLOYEE_ID = E.EMPLOYEE_ID
 25   WHERE STATUS = 'A' AND LVL > 1;

EMPLOYEE_ID EMPLOY
----------- ------
          1 steven
          2 jone

SQL>

1. Привет @tom, не могли бы вы объяснить мне, как это хороший ответ? Когда я удаляю условие: И LVL> 1, запрос возвращает 3 результата. В вашем вопросе нет упоминания о столбце уровня и о том, что он должен быть больше 1? Tejash — тот же вопрос и для вас. Вот скрипка, где вы можете видеть, что она возвращает 3 строки: dbfiddle.uk /… мой ответ возвращает 2 строки. Спасибо. Приветствия.

Ответ №2:

 SELECT E.employee_id, E.EMPLOYEE_NAME
FROM Employee E
JOIN department D ON E.employee_id = D.employee_id
join department T on D.dept_id = T.employee_id
where T.status = 'A'

Вот демонстрация:

ДЕМОНСТРАЦИЯ