Найти всех сотрудников, подотчетных руководителю, включая руководителей

Question

У меня есть таблица как минимум с тремя столбцами: EMPLID, NAME, SUPERVISOR_ID. В одном запросе я хотел бы получить прямые отчеты от менеджеров, включая любые прямые отчеты от подчиненных менеджеров вплоть до самого низкого уровня персонала, когда ему предоставляется менеджер WHERE пункт.

Я могу создать запрос, который покажет, кто сообщает кому:

    SELECT MANAGER.[EMPLID] AS MANAGEREMPLID, MANAGER.[NAME], MANAGER.[SUPERVISOR_ID], STAFF.[EMPLID] AS STAFFEMPLID, STAFF.[NAME], STAFF.[SUPERVISOR_ID]  
  FROM MYHRTABLE AS MANAGER INNER JOIN MYHRTABLE AS STAFF
  ON MANAGER.[EMPLID] = STAFF.[SUPERVISOR_ID]
  ORDER BY MANAGER.[NAME], STAFF.[NAME]

или рекурсивный CTE, который, по-видимому, делает то же самое

 WITH MYCTE
AS ( 
SELECT [EMPLID], [NAME],[SUPERVISOR_ID] FROM 
    (SELECT [EMPLID], [NAME], 
        CASE WHEN [EMPLID] = [SUPERVISOR_ID] THEN NULL ELSE [SUPERVISOR_ID] END AS [SUPERVISOR_ID]              
    FROM MYHRTABLE) AS MYDATA
        WHERE [SUPERVISOR_ID] IS NULL

UNION ALL

    SELECT MYDATA.[EMPLID], MYDATA.[NAME],MYDATA.[SUPERVISOR_ID] FROM 
    (SELECT [EMPLID], [NAME], 
        CASE WHEN [EMPLID] = [SUPERVISOR_ID] THEN NULL ELSE [SUPERVISOR_ID] END AS [SUPERVISOR_ID]      
    FROM MYHRTABLE) AS MYDATA INNER JOIN MYCTE ON MYDATA.[SUPERVISOR_ID] = MyCTE.[EMPLID]
    WHERE MYDATA.[SUPERVISOR_ID] IS NOT NULL)
SELECT * FROM MyCTE

(утверждение случая, потому что у высшего менеджера есть поле наблюдателя, сообщающее себе)

То, что я искал, было способностью дать этому запросу четкое представление и проследить весь путь до персонала, который отчитывается перед этим менеджером. В моих двух примерах я чувствую, что вижу прямые отчеты, но каким-то образом мне нужно сделать еще одно объединение, чтобы увидеть остальную информацию.

И второй вопрос вроде связан с тем, что, исследуя это, кажется, что большинство людей решают найти менеджеров для прямых отчетов с использованием рекурсивного cte, когда я думаю, что это можно было бы решить с помощью первого присоединенного к нему запроса. Учитывая два запроса, которые я разместил, зачем мне использовать рекурсивный CTE для его решения, когда я мог бы использовать ту же таблицу, присоединенную к себе? Кажется, я получаю ту же информацию обратно?

Если это что-то значит, это будет работать на SQL Server 2012 или 2008.

Answer 1

Причина, по которой вы используете рекурсию, заключается в возможности детализации данных путем рекурсивного перехода на менеджер, используемый в качестве привязки, до тех пор, пока не останется больше ничего, чтобы найти и получить выходные данные в виде иерархии.

Запрос на соединение будет ограничен одним уровнем на соединение (попробуйте добавить where manager.emplid = 1 (или ж / д идентификатор менеджера) к нему и понаблюдайте за тем, что происходит). Если вы заранее знаете количество уровней, вы можете использовать левые соединения (по одному соединению на уровень).

Я бы изменил рекурсивное решение этого:

with src as (
  select 
    [emplid]
    , [name]
    , case 
        when [emplid] = [supervisor_id] then null 
        else [supervisor_id] 
     end as [supervisor_id]
  from myhrtable
), cte as ( 
  select [emplid], [name], [supervisor_id], [emplid] as top_manager
  from src
  where [emplid] = 1 -- this is the manager you're interested in
                     -- it can be a sub-manager in the middle or the tree
union all            -- or the top level manager rooting the tree

  select src.[emplid], src.[name], src.[supervisor_id], top_manager
  from src 
  inner join cte on src.[supervisor_id] = cte.[emplid]
  where src.[emplid] <> top_manager
)

select * from cte