/*
新建树形结构表,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;