SQL SERVER：获取树形结构表的层级、索引和序号

/*
   新建树形结构表,id,父节点(parentid)
   节点层级(depth)，广度优先遍历索引(pathindex)，树结构序号(numbericalmapping)
*/
CREATE TABLE tree(
       id int NOT NULL,
       parentid int NULL,
       name nvarchar(300) NOT NULL,
       depth int NULL,
       pathindex int NULL,
       numericalmapping nvarchar(300) NULL
)
--更新根节点的节点层级--
UPDATE tree SET depth = 0
WHERE parentId IS NULL;
--更新其他节点的节点层级--
WHILE EXISTS (SELECT * FROM tree WHERE depth IS NULL)
       UPDATE T SET T.depth = P.depth + 1
              FROM tree AS T INNER JOIN tree AS P
        ON (T.parentId = P.Id)
    WHERE P.depth >= 0
    AND T.depth IS NULL;
--更新根节点的索引--
UPDATE tree SET pathindex = 0, numericalMapping = '0.0'
WHERE parentId IS NULL;
--更新其他节点的索引--
WITH x AS
(
    SELECT id, rank() over (partition by parentId order by id) as pathindex
    FROM tree
    WHERE parentId IS NOT NULL  
)
UPDATE tree
SET pathindex = x.pathindex
FROM x
WHERE tree.id = x.id;
--更新根节点的序号--
UPDATE tree
SET numericalmapping = pathindex
WHERE depth = 1;
--更新其他节点的序号--
WHILE EXISTS (SELECT * FROM tree WHERE numericalMapping Is Null)
    UPDATE T SET T.numericalMapping =   cast(P.numericalmapping as
                                            varchar(300)) + '.' +
                                        cast(T.pathindex as varchar(300))  
              FROM tree AS T INNER JOIN tree AS P
              ON (T.parentId = P.Id)
    WHERE P.pathindex >= 0
    AND T.numericalMapping IS NULL;