Oracle中的SYS_CONNECT_BY_PATH等效查询在SQL Server中如何实现？

Question

我试图将一个涉及Oracle SYS_CONNECT_BY_PATH语法的复杂查询转换为SQL Server：

    SELECT 
           DISTINCT TO_CHAR(CONCAT(@ROOT, SYS_CONNECT_BY_PATH(CONCAT('C_',X), '.'))) AS X_ALIAS
                , TO_CHAR(CONCAT(@ROOT, PRIOR SYS_CONNECT_BY_PATH(CONCAT('C_',X), '.'))) AS X_ALIAS_FATHER
                , TO_CHAR(X) AS X_ALIAS_LEAF
                , LEVEL AS LVL
      FROM MY_TABLE
 LEFT JOIN MY_TABLE_BIS MY_TABLE_BIS_ALIAS ON MY_TABLE_BIS_ALIAS.MY_ID = COL_X
 LEFT JOIN OTHER_TABLE 
        ON OTHER_TABLE.MY_ID = COL_X
CONNECT BY (PRIOR ID_SON = ID_FATHER)
       AND LEVEL <= MAXDEPTH
START WITH ID_FATHER 
        IN (SELECT AN_ID AS ID_FATHER FROM BIG_TABLE)

我使用这个网站得到了以下结果：

WITH n(LEVEL, X_ALIAS, X_ALIAS_FATHER, X_ALIAS_LEAF) AS
      ( SELECT 1, CONCAT('C_',X), CONCAT('C_',X), CAST(X AS VARCHAR(30))
          FROM MY_TABLE
     LEFT JOIN MY_TABLE_BIS MY_TABLE_BIS_ALIAS 
            ON MY_TABLE_BIS_ALIAS.MY_ID = COL_X
     LEFT JOIN OTHER_TABLE 
            ON OTHER_TABLE.MY_ID = COL_X
         WHERE ID_FATHER IN (SELECT AN_ID AS ID_FATHER 
          FROM listAllCfaCfq)
     UNION ALL
        SELECT n.level + 1, n.X_ALIAS + '.' + nplus1.X_ALIAS, n.X_ALIAS_FATHER + '.' + nplus1.X_ALIAS_FATHER, CAST(X AS VARCHAR(30)
          FROM MY_TABLE
     LEFT JOIN MY_TABLE_BIS MY_TABLE_BIS_ALIAS 
            ON MY_TABLE_BIS_ALIAS.MY_ID = COL_X
     LEFT JOIN OTHER_TABLE 
            ON OTHER_TABLE.MY_ID = COL_X AS nplus1, n
         WHERE n.ID_SON = nplus1.ID_FATHER)
        SELECT DISTINCT LEVEL, X_ALIAS, X_ALIAS_FATHER, X_ALIAS_LEAF
         WHERE LEVEL <= @MAXDEPTH;

我更改了表格的名称，可能在此过程中出现了错误，请在评论中告诉我

- Platus

---

第一个查询没有引用 listAllCfaCfq。请提供表的 DDL。 - Dan Guzman

可以提供表格数据吗？ - Ranjana Ghimire

如果您在SQL Server中使用了hierarchyid（我无法确定，因为您没有发布表结构），那么您可以使用CAST（hierarchyid as nvarchar（100））来显示每行的路径，例如：/2/1/2/1/ - Steve Ford

不知道为什么这么多人点赞了这条评论。直到DDL被提供之前，它应该被关闭。 - Martin Smith

我投票关闭这两个问题，因为它们需要提供DDL，而且从来没有真正提出问题。而且已经过去4年了。 - RBarryYoung

Answer 1

CREATE TABLE #MY_TABLE
(
     ID         INT
    ,ID_FATHER  INT
    ,COL_X      INT
)

CREATE TABLE #MY_TABLE_BIS
(
     MY_ID  INT
    ,X      VARCHAR(50)
)

CREATE TABLE #OTHER_TABLE
(
     MY_ID  INT
    ,[ROOT] VARCHAR(50)
)

CREATE TABLE #BIG_TABLE
(
     AN_ID  INT
)
go

DECLARE @MAXDEPTH INT = 10

;WITH
    cte_prepare
    AS
    (
        SELECT
             ID
            ,ID_FATHER
            ,TB.X
            ,OT.[ROOT]
        FROM #MY_TABLE T
        LEFT JOIN #MY_TABLE_BIS TB
                    ON TB.MY_ID = COL_X
        LEFT JOIN #OTHER_TABLE OT
                    ON OT.MY_ID = COL_X
    ),
    cte_connect (LVL, ID, X_ALIAS, X_ALIAS_FATHER, X_ALIAS_LEAF)
    AS
    (
        SELECT 1
            ,T.ID
            ,CAST(CONCAT(T.[ROOT], '.C_', T.X) AS VARCHAR(MAX))
            ,CAST(CONCAT(T.[ROOT], '.') AS VARCHAR(MAX))
            ,CAST(T.X AS VARCHAR(30))
        FROM cte_prepare T
        WHERE T.ID_FATHER IN
            (
                SELECT AN_ID AS ID_FATHER
                FROM #BIG_TABLE
            )

        UNION ALL

        SELECT F.LVL + 1
             ,S.ID
             ,CAST(CONCAT(F.X_ALIAS, '.C_' + S.X) AS VARCHAR(MAX))
             ,CAST(F.X_ALIAS AS VARCHAR(MAX))
             ,CAST(S.X AS VARCHAR(30))
        FROM cte_prepare S
            INNER JOIN cte_connect F
                        ON S.ID_FATHER = F.ID
        WHERE F.LVL < @MAXDEPTH
    )
SELECT DISTINCT
     CT.X_ALIAS
    ,CT.X_ALIAS_FATHER
    ,CT.X_ALIAS_LEAF
    ,CT.LVL
FROM cte_connect CT
-- Uncomment this and set depth when it's greater than 100.
--OPTION (MAXRECURSION 0) -- value between 0 and 32,767 (default is 100), 0 = unlimited

参见 https://learn.microsoft.com/zh-cn/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

- Павел Сивоплясов

Answer 2

对于层次结构部分，您可能需要使用递归CTE，然后使用FOR XML进行路径构建。 FOR XML旨在将结果转换为XML，但您可以使用它将结果转换为各种有趣的文本格式。

SQL Server中的FOR XML PATH子句

FOR XML（SQL Server）Microsoft参考

- Nick Fotopoulos

Answer 3

在这里你会找到一个完整的示例，展示了如何将使用CONNECT BY语法的查询转换为使用SQLServer递归CTE的方法，而无需编写额外的函数。

- p3consulting

Answer 4

所有问题都可以使用几个函数来解决，通过一些递归也很简单，（请注意T-SQL中的最大递归级别为32）

假设我们有以下表格：（针对一个非常小的公司）

TableName: Employees
id.....name..............manager_id
1      Big Boss          NULL
2      Sales Manager     1
3      Support Manager   1
4      R&D Manager       1               
5      Sales man         2
6      Support man       3
7      R&D Team leader   4
8      QA Team leader    4
9      C Developer       7
10     QA man            8
11     Java Developer    7      

我们只需要一个函数来检查两个ID之间是否存在链接，以及另一个函数来给出从一个ID到另一个ID的路径。

第一个函数非常简单，可以使用递归实现：

Create Function dbo.Do_WE_Have_path(@id int, @boss_id int, @max_level int) returns int
Begin
  declare @res int, @man int
  set @res = 0
  if @id = @boss_id 
    set @res = 1
  else if @max_level > 0 
  Begin
    Select @man=manager_id from Employees where id=@id
    set @res = Do_WE_Have_path(@man, @boss_id, @max_level-1) --recursion
  End
  return res 
End

使用上述函数，我们可以选择所有连接长度短于或等于指定级别的实体，因此现在我们可以编写一个方法来构建路径（如果存在），请注意应使用上述方法过滤不存在的路径。

Create Function dbo.Make_The_path(@id int, @boss_id int, @max_level int) returns varchar(max)
Begin
  declare @res varchar(max), @man int
  select @res = name from Employees where id=@id
  if max_level > 0 AND @id <> @boss_id
  Begin 
    select @man = manager_id from Employees where id = @id
    set @res = dbo.Make_The_path(@man, @boss_id, max_level-1) + '/' + @res
  End
  return @res
End

现在我们可以使用这两个函数来获取从老板到工人的路径：

Select dbo.Make_The_path(id, 1, 3) Where Do_WE_Have_path(id, 1, 3)=1    

这两个函数可以合并成一个，也许你需要为每个结构重新编写它，但重要的是它是可行的。

- SHR