应用场景：一般是菜单、组织层级、上下级权限等功能使用，数据库结构就是有id和pid（上级ID）
虽然说用循环语句也能解决，但逻辑还是不够简洁，下面贴上SQL代码：
WITH cte AS ( SELECT OrgId , ParentId , IsArea , IsActive , sort , CAST( OrgId AS NVARCHAR(MAX)) path, 1 AS OrgLevel FROM dbo.SysOrg WHERE ParentId = 0 UNION ALL SELECT o.OrgId , o.ParentId , o.IsArea , o.IsActive , o.sort , c.path + '' + CAST( o.OrgId AS NVARCHAR(MAX)) path,-- 递归，路径为父路径+当前路径 c.OrgLevel+1 AS OrgLevel -- 递归，层数为父级层数+1 FROM SysOrg o JOIN cte c ON o.ParentId = c.OrgId ) SELECT * FROM cte;
查询出来的数据效果：
附：还可用于批量快速更新，如下：
WITH cte AS ( SELECT OrgId , ParentId , IsArea , IsActive , sort , CAST( OrgId AS NVARCHAR(MAX)) path, 1 AS OrgLevel FROM dbo.SysOrg WHERE ParentId = 0 UNION ALL SELECT o.OrgId , o.ParentId , o.IsArea , o.IsActive , o.sort , c.path + '' + CAST( o.OrgId AS NVARCHAR(MAX)) path,-- 递归，路径为父路径+当前路径 c.OrgLevel+1 AS OrgLevel -- 递归，层数为父级层数+1 FROM SysOrg o JOIN cte c ON o.ParentId = c.OrgId ) UPDATE dbo.SysOrg SET dbo.SysOrg.PATH=cte.path,OrgLevel =cte.OrgLevel FROM cte WHERE dbo.SysOrg.OrgId=cte.OrgId