Получите более высокое значение, если первое значение не соответствует критериям
Я работаю над запросом для получения информации о сотрудниках, в том числе о том, кто может утверждать табели учета рабочего времени сотрудников. В нашей базе данных указано, кому подчиняется сотрудник, но этот человек не обязательно будет тем, кто может утверждать табели учета рабочего времени. Данные выглядят так
В этом примере только должности «Супер» и «PM» могут утверждать табели учета рабочего времени. Поскольку сотрудники 1 и 2 подчиняются сотруднику 3, который является бригадиром и не может утверждать табели учета рабочего времени, мне нужен запрос, который будет изучать данные сотрудника 3, чтобы узнать, кому они подчиняются, в данном случае сотруднику 6, который является суперменом и может поэтому одобряйте их табели учета рабочего времени. Таким образом, окончательный результат запроса должен выглядеть следующим образом:
И хотя это не часто встречается, бывают случаи, когда может быть третий «уровень», поэтому я не могу просто выполнить оператор «IF», чтобы подняться на один уровень. Ему нужно идти вверх, пока он не найдет «подчиненных», положение которых позволяет им утверждать табели учета рабочего времени.
Я пробовал выполнять рекурсивный поиск, но они не извлекали информацию правильно, и, к сожалению, я недостаточно хорошо понимаю рекурсивный поиск, чтобы объяснить, что происходит с возвращаемыми данными.
Я также предпочел бы не использовать временные таблицы, поскольку этот запрос потребуется использовать нескольким людям, и у некоторых из них нет уровня доступа для создания и удаления временных таблиц.
Ответы 2
Это должно идти по дереву, пока вы не найдете подходящего утверждающего:
with tree as (
select EENum as EEBase, EENum, ReportsTo, 'N' as approver
from Employees
union all
select EEBase, e.EENum, e.ReportsTo,
case when e.Position in ('PM', 'Super') then 'Y' else 'N' end
from tree t inner join Employees e on e.EENum = t.ReportsTo
where t.approver = 'N'
)
select EEBase as EENum, EENum as ApprovedBy
from tree
where approver = 'Y';
У данных есть утверждающий, который отчитывается перед самим собой. Это может завершиться при первом проходе. Если у вас есть такой цикл на неодобрительной позиции, это приведет к ошибке. Я не понимаю цели этого (обычно верхний уровень имеет значение null) и не знаю, должен ли запрос обрабатывать какой-либо из этих сценариев по-разному.
Когда я пробую это, я получаю максимальную ошибку рекурсии независимо от того, насколько высоко я устанавливаю эту опцию.
@JoelCoehoorn Возможно, я исправил это, исправив один из псевдонимов всего за несколько минут до вашего комментария.
Мне все еще не удается: dbfiddle.uk/SCUX3W3Q
Кроме того: идея вопроса заключается в том, что у одного работника может быть более одного утверждающего в своем дереве, и он хочет остановиться на первом утверждающем.
@JoelCoehoorn Похоже, сотрудник 8 отчитывается перед самим собой. Да, я знаю, что остановлюсь, как только таковой будет найден.
Если я изменю данные, все равно не получится, поэтому 8 отчетов будут равны NULL dbfiddle.uk/iNyaIFUO
@shawnt00 большое спасибо! Мне удалось заставить это работать в реальной базе данных, и, судя по моим тестам, это сработало. Я планирую более подробно рассмотреть ваше решение, чтобы лучше понять рекурсию в целом. Я не могу проголосовать за ваш ответ прямо сейчас, так как у меня недостаточно репутации, поэтому мне придется попытаться получить еще немного.
@JoelCoehoorn Спасибо за устранение неполадок. Первоначально я думал, что максимальная рекурсия выполнена из-за изменений, которые мне пришлось внести при работе с полным набором данных.
@GrantBaugh, вы не можете проголосовать за него (в любом случае это необязательно), но вы можете «принять» его, нажав галочку под стрелками голосования.
@DaleK Спасибо!
Вы можете использовать следующий рекурсивный запрос CTE, чтобы получить ожидаемые результаты. Я даже включил еще один уровень в свои тестовые данные, и он работает очень хорошо.
WITH ApproverCTE AS (
-- Anchor member: Start with all employees
SELECT
E.EENum AS EmployeeID,
E.Position,
E.ReportsTo,
E.EENum AS RootEmployeeID -- Keep track of the original employee
FROM
Employees E
UNION ALL
-- Recursive member: Move up the hierarchy until we find a "Super" or "PM"
SELECT
E.EENum AS EmployeeID,
E.Position,
E.ReportsTo,
CTE.RootEmployeeID
FROM
Employees E
INNER JOIN
ApproverCTE CTE ON E.EENum = CTE.ReportsTo
WHERE
E.Position NOT IN ('Super', 'PM') -- Continue recursion if the position isn't a Super or PM
)
-- Final selection: Find the approver for each employee
SELECT
CTE.RootEmployeeID AS EmployeeID,
E.EENum AS ApprovedBy
FROM
ApproverCTE CTE
INNER JOIN
Employees E ON CTE.ReportsTo = E.EENum
WHERE
E.Position IN ('Super', 'PM') -- Select only approvers who are Super or PM
ORDER BY
EmployeeID;
Пожалуйста, проверьте dbFiddle здесь.
Пользователь, сообщающий самому себе, а не NULL, был осознанным выбором в нашем наборе данных по причинам, в которые я не буду вдаваться. Он применяется даже реже, чем можно предположить из примера набора данных, но, поскольку это ДЕЙСТВИТЕЛЬНО случается, я включил один из них в качестве примера, чтобы гарантировать, что он был учтен в ответах.