Блог

Главная - Вопросы по программированию - Эквивалент SYS_CONNECT_BY_PATH в t-SQL

Эквивалент SYS_CONNECT_BY_PATH в t-SQL

#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