SQL 获取所有上级的实现方法
说明:
(1) 可以做成一个函数,直接调用即可;
(2) M0Org 是数据库存在的表 ,M0OrgPID 为目标表,OrgID 为 ID 字段,PID 为上级 ID 字段
- DECLARE @OrgID NVARCHAR(36)= '00000000-0000-0000-00000002205223459';
- --获取当前机构的所有上级 放入M0OrgPID
- WITH M0OrgPID
- AS ( SELECT *, 0 AS lvl
- FROM M0Org
- WHERE OrgID = @OrgID
- UNION ALL
- SELECT d.*, lvl + 1
- FROM M0OrgPID c
- INNER JOIN M0Org d ON c.PID = d.OrgID )
- SELECT * FROM M0OrgPID;
来源: http://www.92to.com/bangong/2017/04-27/20971891.html