T-SQL根据重构树节点的层级、路径、左右值(SQL Server)

CREATE TABLE [dbo].[Tree](
	[ID] [int] IDENTITY(1,1) NOT NULL,	--节点ID
	[tName] [nvarchar](50) NULL,		--节点名称
	[parentID] [int] NOT NULL,		--节点的父节点ID（0：根节点）
	[tlevel] [int] NULL,			--节点所处层级（0：根节点）
	[pathnode] [nvarchar](max) NULL,	--节点路径（格式：,1,2,）
	[lft] [int] NULL,			--节点左值
	[rgt] [int] NULL			--节点右值
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

insert into Tree(ID,tName,parentID) values(1,'Food',0);
insert into Tree(ID,tName,parentID) values(2,'Fruit',1);
insert into Tree(ID,tName,parentID) values(3,'Red',2);
insert into Tree(ID,tName,parentID) values(4,'Yellow',2);
insert into Tree(ID,tName,parentID) values(5,'Cherry',3);
insert into Tree(ID,tName,parentID) values(6,'Banana',4);
insert into Tree(ID,tName,parentID) values(7,'Meat',1);
insert into Tree(ID,tName,parentID) values(8,'Beef',7);
insert into Tree(ID,tName,parentID) values(9,'Pork',7);
insert into Tree(ID,tName,parentID) values(10,'Mobile',0);
insert into Tree(ID,tName,parentID) values(11,'Xiaomi',10);
insert into Tree(ID,tName,parentID) values(12,'RedMi',11);
insert into Tree(ID,tName,parentID) values(13,'Apple',10);

declare @tb TABLE (
    ID int NOT NULL,			--节点ID
    tName nvarchar(50) NULL,	        --节点名称
    parentID int NOT NULL,		--节点的父节点ID（0：根节点）
    tlevel int,				--节点所处层级（0：根节点）
    pathnode nvarchar(max),		--节点路径（格式：,1,2,）
    lft int,			        --节点左值
    rgt int				--节点右值
);

--树数据放入表变量
insert into @tb(ID,tName,parentID) 
SELECT t.ID,t.tName,t.parentID 
from Tree t;

--根据parentID生成tlevel、pathnode
WITH pathTree(ID,tName,parentID,level,pathnode) as
(
    SELECT ID,tName,parentID,0 level,','+cast(ID as nvarchar(max))+','
    FROM @tb WHERE parentID =0
    UNION ALL
    SELECT a.ID,a.tName,a.parentID,b.level+1,cast(b.pathnode +cast(A.ID as nvarchar)+',' as nvarchar(max)) 
    FROM @tb a
    join pathTree b on a.parentID=b.ID
)
update @tb set tlevel=pt.level,pathnode=pt.pathnode
from @tb t
join pathTree pt on t.ID=pt.ID;

--根据parentID、tlevel生成lft、rgt
WHILE exists(SELECT ID FROM @tb where lft is null and rgt is null)
BEGIN
    update a set a.lft=c.lft,a.rgt=c.rgt
    from @tb a 
    join (
        SELECT top 1 ID
            ,case 
                --根节点
                when b.tlevel=0 
                    then (
                        select case 
                            --第一棵树,左值为1
                            when max(rgt) is null then 1 
                            --非第一棵树,左值为现有最大右值+1
                            else max(rgt)+1 
                        end from @tb
                    )
                --非根节点
                else (
                    select case 
                        --同级节点第一个节点,左值为父节点左值+1
                        when max(rgt) is null then (select lft+1 from @tb ta where b.parentID=ta.ID)
                        --非同级节点第一个节点,左值为同级节点最大右值+1
                        else max(rgt)+1
                    end from @tb 
                    where parentID = b.parentID
                )  
            end as lft --左值
            ,case 
                --根节点,子节点数量*2+左值-1
                when b.tlevel=0 then (
                    select count(1)*2+(
                        --见根节点左值
                        select case 
                                when max(rgt) is null then 1 
                                else max(rgt) + 1
                            end 
                        from @tb
                    )-1 
                    from @tb a 
                    where a.pathnode like '%,'+cast(b.ID as nvarchar)+',%') 
                --非跟节点,子节点数量*2+左值-1
                else (
                    select count(1)*2+(
                        --见非根节点左值
                        select case 
                            when max(rgt) is null then (select lft+1 from @tb ta where b.parentID=ta.ID)
                            else max(rgt)+1
                        end 
                        from @tb where parentID = b.parentID						
                    )-1 
                    from @tb a 
                    where 
                    a.pathnode like '%,'+cast(b.ID as nvarchar)+',%'
                ) 
            end as rgt --树根的右键
        FROM @tb  b 
        where b.lft is null and b.rgt is null
        order by b.tlevel asc	--逐层生成左右值
    ) c on a.ID=c.ID
    --select * from @tb;
    
END

update t set t.tlevel=tb.tlevel,
    t.pathnode=tb.pathnode,
    t.lft=tb.lft,
    t.rgt=tb.rgt
from Tree t
join @tb tb on t.ID=tb.ID;

select * from Tree