t-SQL中的SYS_CONNECT_BY_PATH等效项 [英] SYS_CONNECT_BY_PATH equivalent in t-SQL

问题描述

我需要将Oracle查询转换为SQL Server.

I need to convert my Oracle query into SQL Server.

查询生成管理层次".基于用户名.输出将具有一个管理链，最后一个元素是当前经理.

The query generates a "management hierarchy" based on usernames. The output will have a management chain with the last element being the current manager.

表格:

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查询:

My Oracle query for this:

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等效项是什么?

What would be the SQL Server equivalent of this query?

推荐答案

;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
;

如果要进行大量递归，则应查看默认值为100的 MAXRECURSION .

If you are doing a lot of recursion, you should take a look at MAXRECURSION which has a default of 100.

这篇关于t-SQL中的SYS_CONNECT_BY_PATH等效项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！