Recursive data query of tree structure in a table in SQL Server

demand ： A data table is a tree structure , namely id,pid, Each record will have its own parent record . Now let's take the specified record and the record as the parent , Query all subordinate level records .

solve ： It's used in this sqlserver Of with as Statements and union all Merge

WITH    Emp     
          AS ( SELECT * FROM  dbo.Users WHERE    ID = '111'  
               UNION ALL   
               SELECT   d.*  FROM Emp INNER JOIN dbo.Users d ON d.pid= Emp.ID     
             )                     SELECT *   FROM    Emp