真爱无限的知识驿站

应用场景：一般是菜单、组织层级、上下级权限等功能使用，数据库结构就是有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