SQLSERVER数据处理 刷新组织层级

– 声明变量
DECLARE
@BuCode AS varchar(max)
– 声明游标
DECLARE C_Employees CURSOR FAST_FORWARD FOR
SELECT BUCode
FROM myBusinessUnit WHERE ParentGUID IS NOT NULL;
OPEN C_Employees;
– 取第一条记录
FETCH NEXT FROM C_Employees INTO @BuCode;
WHILE @@FETCH_STATUS = 0
BEGIN
–PRINT @BuCode
– 操作
DECLARE
@HierarchyCode AS varchar(max)
–递归找到所有父级组织
with cte AS
(
select ParentGuid,BUCode,0 as lvl from myBusinessUnit – 子表
where BUCode =@BuCode
union ALL
select d.ParentGuid,d.BUCode,lvl+1
from cte c – 子表结果集
inner join myBusinessUnit d – 父表
on c.ParentGuid = d.BUGUID
)
select @HierarchyCode = (select top 1 (select stuff((SELECT ‘.’+ BUCode from cte order by lvl desc for xml path(’’)),1,1,’’))
from cte) – 父表结果集
UPDATE myBusinessUnit SET HierarchyCode = @HierarchyCode WHERE BUCode=@BuCode;
– 取下一条记录
FETCH NEXT FROM C_Employees INTO @BuCode;
END
– 关闭游标
CLOSE C_Employees;
– 释放游标
DEALLOCATE C_Employees;