递归 t-sql 查询

问题描述

我有一张如下所示的表格.

I have a table as shown below.

ID ParentID Node Name  Node Type
------------------------------------------------------------------
525 524  Root   Area Level 1
526 525  C   Area Level 2
527 525  A   Area Level 2
528 525  D   Area Level 2
671 525  E   Area Level 2
660 527  B   Area Level 3
672 671  F   Area Level 3

如何编写递归 t-sql 查询以生成以下输出?

How can i write a recursive t-sql query to generate below output?

输出(输出中不需要根"节点):

Output ("Root" node not required in the output):

Node  ID
-----------------------
A  527
A/B  660
C  526
D  528
E  671
E/F  672

谢谢

推荐答案

查看 本页 使用公共表表达式.这就是我会使用的(假设您至少使用 SQL Server 2005)

Take a look at this page on using common table expressions. That is what I would use (assuming you are using at least SQL Server 2005)

这是使用您的案例的代码示例:

Here is a code example using your case:

 WITH CTE (NodePath, ID) AS (
    SELECT
        '/' + CAST(NodeName AS NVARCHAR(MAX)) AS NodePath,
        ID
    FROM TABLE
    WHERE NodeName = 'Root'

    UNION ALL

    SELECT
        CTE.NodePath + '/' + CAST(NodeName AS NVARCHAR(MAX)) AS NodePath,
        TABLE.ID
    FROM CTE
    INNER JOIN TABLE ON TABLE.ParentId = CTE.ID
)

SELECT
    NodeName,
    ID
FROM CTE