SQL in MsSql to obtain all superior instance codes

This article mainly introduces the relevant information on the implementation method of SQL to obtain all superiors. Friends who need it can refer to

SQL to obtain the implementation method of all superiors

Instructions:

(1) can be made into a function and called directly;

(2) M0Org is a table that exists in the database, M0OrgPID is the target table, OrgID is the ID field, and PID is the superior ID field

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;

The above is the detailed content of SQL in MsSql to obtain all superior instance codes. For more information, please follow other related articles on the PHP Chinese website!