自引用表中的分层数据以树形式呈现

Question

看起来所有简单情况下都已经有人提出并回答了，但我遇到的问题还没有解决。我尝试使用递归CTE生成这个表格，但是也许使用游标会更好？或者一组递归函数可以解决问题？

这可以在CTE中完成吗？

考虑以下表格：

PrimaryKey   ParentKey  
1            NULL       
2            1       
3            6
4            7
5            2
6            1
7            NULL

应该产生。

PK
1
-2
--5
-6
--3
7
-4

在这里，-符号的数量等于深度，我的主要困难是排序。

- Beta033

Answer 1

使用邻接表模式处理有点笨拙，但可以通过递归CTE完成。

WITH Hierarchy AS
(
    SELECT
        PrimaryKey, ParentKey,
        CAST('/' + CAST(PrimaryKey AS varchar(10)) AS varchar(50)) AS [Path],
        CAST('' AS varchar(50)) AS Prefix
    FROM @Tbl
    WHERE ParentKey IS NULL

    UNION ALL

    SELECT
        t.PrimaryKey, t.ParentKey,
        CAST(h.[Path] + '/' + CAST(t.PrimaryKey AS varchar(10)) AS varchar(50)),
        CAST(h.Prefix + '-' AS varchar(50))
    FROM Hierarchy h
    INNER JOIN @Tbl t
        ON t.ParentKey = h.PrimaryKey
)
SELECT [Path], Prefix + CAST(PrimaryKey AS varchar(10)) AS Node
FROM Hierarchy
ORDER BY [Path]

为了获得你想要的精确结果，你需要同时使用“前缀”列（用于缩进）和代理“路径”列，后者实际上进行排序。

- Aaronaught

Answer 2

这是我的建议 - 如果需要的话，您还可以获取每个节点的路径。更改str(x,4)调用 - 当转换为十进制时，将4设置为主键的最大长度。

WITH TreePrinter(id, parent, path, prefix) AS
(
   SELECT 
      PrimaryKey, ParentKey, 
      CAST(str(PrimaryKey,4) AS varchar(max)),
      CAST('' AS varchar(max))
   FROM YourTable
   WHERE ParentKey IS NULL
   UNION ALL 
   SELECT child.PrimaryKey, child.ParentKey, 
     CAST(parent.path+'/'+STR(child.PrimaryKey,4) AS varchar(max)),
     CAST(parent.prefix+'-' AS varchar(max)),
     FROM YourTable parent
     INNER JOIN TreePrinter child ON child.id=parent.ParentKey
)
SELECT prefix+str(id) FROM TreePrinter
ORDER BY path 

- mdma