#sql #sql-server #tsql #common-table-expression #recursive-query
#sql #sql-сервер #tsql #common-table-expression #рекурсивный запрос
Вопрос:
Мне нужно преобразовать мой запрос Oracle в SQL Server.
Запрос генерирует «иерархию управления» на основе имен пользователей. На выходе будет цепочка управления, последним элементом которой является текущий менеджер.
Таблица:
EE_USERNAME MGR_USERNAME
user1 ceo
user2 user1
user3 user2
user4 user1
user5 user2
user6 user3
Пример вывода:
user1: ceo
user2: ceo,user1
user3: ceo,user1,user2
user4: ceo,user1
user5: ceo,user1,user2
user6: ceo,user1,user2,user3
Мой запрос Oracle для этого:
SELECT EMPLOYEE_DATA.* , SYS_CONNECT_BY_PATH(MGR_USERNAME, ',') "Path"
FROM EMPLOYEE_DATA
START WITH MGR_USERNAME = 'ceo'
CONNECT BY NOCYCLE prior EE_USERNAME = MGR_USERNAME
Каким будет эквивалент SQL Server этого запроса?
Комментарии:
1. Вероятно, вы хотите исследовать рекурсивный CTE.
Ответ №1:
;WITH cte AS (
SELECT e.*, mgr.EE_USERNAME AS Path
FROM EMPLOYEE_DATA AS mgr
JOIN EMPLOYEE_DATA AS e ON mgr.EE_USERNAME = e.MGR_USERNAME
WHERE mgr.EE_USERNAME = 'ceo'
UNION ALL
SELECT e.*, mgr.Path ',' mgr.EE_USERNAME AS Path
FROM cte AS mgr
JOIN EMPLOYEE_DATA e ON mgr.EE_USERNAME = e.MGR_USERNAME
)
SELECT *
FROM cte
-- OPTION (MAXRECURSION 200) -- if you need a different value
;
Если вы выполняете много рекурсии, вам следует взглянуть на MAXRECURSION
значение по умолчанию 100.
Комментарии:
1. Спасибо, это работает! Ожидается ли, что время обработки запроса будет намного медленнее, чем у Oracle?
2. Я новичок в переполнении стека. Я не могу найти вариант для принятия ответа и закрытия потока, не могли бы вы направить меня? Спасибо!
3. Флажок слева. Вероятно, тот же perf, т. Е. Хороший, но не отличный, и его следует избегать, за исключением этих типов запросов. Он использует очень быструю временную таблицу под капотом, но с большими объемами данных или большим количеством рекурсии это может быть медленным. Не забудьте посмотреть
OPTION (MAXRECURSION...
Ответ №2:
Запрос Charlieface опускает функциональность, предоставляемую директивой Oracle NOCYCLE, в которой говорится о прекращении рекурсии, если одна и та же запись возвращается во второй раз.
Если вы хотите эту защиту, вы можете добавить предложение Where в рекурсивный раздел запроса CTE:
Where Instr(mgr.EE_Username,mgr.Path)=0