Как написать рекурсивный запрос?

#sql #sql_server

Есть таблица иерархии dept_employees  

| ID | parent_id | obj_id | obj_type |  
+----+-----------+--------+----------+
| 10 | null      | 1      | dept     |
| 11 | 10        | 2      | dept     |
| 12 | 11        | 1      | emp      |
| 13 | 11        | 2      | emp      |
| 14 | 13        | 3      | emp      | 
| 15 | 12        | 4      | emp      | 


В иерархической таблице храниться список департаментов и сотрудников
obj_id ссылается на таблицы employees и ogpo_dept
И есть таблицы сотрудников, департаментов и кассовых терминалов:  

dbo.employees             dbo.ogpo_dept      dbo.cash_extensions 
| ID | FIO  | dept_id |   | ID | Name   |    | ID | FIO     |
+----+------+---------|   +----+--------+    +----+---------+
| 1  | Аня  | 1       |   | 1  | ГО     |    | 1  | Аня     |
| 2  | Ира  | 1       |   | 2  | Астана |    | 2  | Макс    |
| 3  | Макс | 1       |                      | 3  | Юля     |
| 4  | Юля  | 1       |


Мне нужно передать id сотрудника и вытащить из иерархической таблицы смотреть от
сотрудника(которое передали ID) вышестоящего и нижестоящих сотрудников которые есть
в таблице dbo.cash_extensions
Мой рекурсивный запрос:  

with cteTop as ( --первый cte выбирает вышестоящую сущность
select de.id, de.parent_id, de.obj_id
from [dbo].[dept_employees] de
where de.id = 12
union all
select de.id, de.parent_id, de.obj_id
from [dbo].[dept_employees] de
inner join cteTop c on c.parent_id = de.id
),

cteBot as ( -- выбираем нижестоящих 
select de.id, de.parent_id, de.obj_id
from [dbo].[dept_employees] de
where de.id = 12
union all
select de.id, de.parent_id, de.obj_id
from [dbo].[dept_employees] de
inner join cteBot c on c.id = de.parent_id
)

select e.fio
from cteTop
left join ogpo_dept od on od.id = cteTop.obj_id 
left join employees e  on e.dept_id = od.id
inner join Cash.dbo.cash_extensions ce on ce.fio=e.fio
union all
select e.fio
from cteBot
left join ogpo_dept od on od.id = cteBot.obj_id
left join employees e on e.dept_id = od.id
inner join cash_extensions ce on ce.fio=e.fio


Результат запроса :  

| FIO | 
+-----+ 
| Аня |
| Юля |     


Результат должен быть:  

| FIO  | 
+------+ 
| Аня  |
| Юля  |
| Макс |    


Вопрос как вытащить всех сотрудников вышестоящего филиала и нижестоящих сотрудников
от текущего сотрудника?
    

Ответы

Ответ 1

Кратко опишу алгоритм.

Если я правильно понял, задача состоит из трёх частей:


От переданного ИДа идём вверх по дереву, пока не найдём департамент.
от департамента находим всех его детей, выбираем из них только с типом employee
выбираем тех, кто есть в таблице cache_extensions


пункты 1. и 2. первая и вторая рекурсивная часть рекурсивного СТЕ:

--таблички

USE tempdb;
IF OBJECT_ID('dept_employees')IS NOT NULL DROP TABLE dept_employees
IF OBJECT_ID('employees')IS NOT NULL DROP TABLE employees
IF OBJECT_ID('ogpo_dept')IS NOT NULL DROP TABLE ogpo_dept
IF OBJECT_ID('cash_extensions')IS NOT NULL DROP TABLE cash_extensions
CREATE TABLE dept_employees(
  ID INT NOT NULL,
  parent_id INT NULL,
  obj_id INT NOT NULL,
  obj_type VARCHAR(4) NOT NULL
)
CREATE TABLE employees(
  ID INT NOT NULL,
  FIO VARCHAR(255) NOT NULL,
  dept_id INT NOT NULL
)
/*CREATE TABLE ogpo_dept(
  ID INT NOT NULL,
  Name VARCHAR(255) NOT NULL
)*/
CREATE TABLE cash_extensions(
  ID INT NOT NULL,
  FIO VARCHAR(255) NOT NULL
)


--данные

INSERT dept_employees VALUES
--depts
(10, NULL, 1, 'dept'),
(20, 10,    2, 'dept'),
(30, NULL, 3, 'dept'),
(40, 30,    4, 'dept'),
--emps
(50, 20, 1, 'emp'),
(60, 50, 2, 'emp'),
(70, 50, 3, 'emp'),
(80, 40, 4, 'emp'),
(90, 80, 5, 'emp'),
(100,30, 6, 'emp')
INSERT employees VALUES
(1, 'd1->d2->p1', 2),
(2, 'd1->d2->p1->p2', 2),
(3, 'd1->d2->p1->p3', 2),
(4, 'd3->d4->p4', 4),
(5, 'd3->d4->p4->p5', 4),
(6, 'd3->p6', 3)
INSERT cash_extensions VALUES
(1, 'd1->d2->p1'),
(2, 'd1->d2->p1->p2'),
(3, 'd1->d2->p1->p3'),
(4, 'd3->d4->p4'),
(5, 'd3->d4->p4->p5'),
(6, 'd3->p6')


--процедура

IF OBJECT_ID('FindAllCasheExtemsionsFromDeptByEmployee') IS NOT NULL DROP PROC FindAllCasheExtemsionsFromDeptByEmployee
GO
CREATE PROC FindAllCasheExtemsionsFromDeptByEmployee 
  @EmployeeId INT
AS
WITH CTE AS(
  SELECT 
    CASE obj_type 
      WHEN 'emp' THEN 'find_depart' 
      WHEN 'dept' THEN 'find_childs'
    END what, id AS child_Id, parent_id AS parend_id, obj_id, E.obj_type
  FROM dept_employees E
  WHERE id = @EmployeeId

  UNION ALL

  --идём вверх по дереву до первого департамента
  SELECT 
    CASE E.obj_type 
      WHEN 'emp' THEN 'find_depart' 
      WHEN 'dept' THEN 'find_childs'
    END what, id AS child_Id, parent_id AS parend_id, E.obj_id, E.obj_type
  FROM CTE
    JOIN dept_employees E ON CTE.parend_id = E.ID
  WHERE what = 'find_depart'

  UNION ALL

  --теперь идём вниз по дереву, находим всех потомков
  SELECT 'find_childs' what, id AS child_Id, parent_id AS parend_id, E.obj_id, E.obj_type
  FROM CTE
    JOIN dept_employees E ON CTE.child_id = E.parent_id
  WHERE what = 'find_childs'
)
SELECT E.*
FROM CTE
  JOIN employees E ON CTE.obj_id = E.ID
  JOIN cash_extensions C ON E.FIO = C.FIO
WHERE CTE.obj_type = 'emp' AND CTE.what = 'find_childs'



OPTION (MAXRECURSION 0) 
GO


--тесты и результаты

EXEC FindAllCasheExtemsionsFromDeptByEmployee 10 -- d1
/*
ID     FIO    dept_id
1      d1->d2->p1   2
2      d1->d2->p1->p2      2
3      d1->d2->p1->p3      2
*/
EXEC FindAllCasheExtemsionsFromDeptByEmployee 50 -- p1
/*
ID     FIO    dept_id
1      d1->d2->p1   2
2      d1->d2->p1->p2      2
3      d1->d2->p1->p3      2
*/
EXEC FindAllCasheExtemsionsFromDeptByEmployee 70 -- p3
/*
ID     FIO    dept_id
1      d1->d2->p1   2
2      d1->d2->p1->p2      2
3      d1->d2->p1->p3      2
*/
EXEC FindAllCasheExtemsionsFromDeptByEmployee 40 -- d4
/*
ID     FIO    dept_id
4      d3->d4->p4   4
5      d3->d4->p4->p5      4
*/
EXEC FindAllCasheExtemsionsFromDeptByEmployee 90 -- p5
/*
ID     FIO    dept_id
4      d3->d4->p4   4
5      d3->d4->p4->p5      4
*/
EXEC FindAllCasheExtemsionsFromDeptByEmployee 100 -- p6
/*
ID     FIO    dept_id
4      d3->d4->p4   4
5      d3->d4->p4->p5      4
6      d3->p6 3
*/


пс: задача очень плохо описана.


Ответ 2

Мой запрос, передал переменную :  

declare @deptID int
with cteTop as ( 
select de.id, de.parent_id, de.obj_id
from [dbo].[dept_employees] de
where de.id = 12
union all
select de.id, de.parent_id, de.obj_id
from [dbo].[dept_employees] de
inner join cteTop c on c.parent_id = de.id
)
select @deptID = cteTop.id from cteTop; --нашли департамент
with cteBot as ( 
select de.id, de.parent_id, de.obj_id
from [dbo].[dept_employees] de
where de.id = @deptID
union all
select de.id, de.parent_id, de.obj_id
from [dbo].[dept_employees] de
inner join cteBot c on c.id = de.parent_id
)

select e.fio
from cteBot
left join employees e on e.id = cteBot.obj_id and cteBot.obj_type = 'emp'
inner join cash_extensions ce on ce.fio=e.fio