1 with cte as 2 ( 3 select a.DepartCode,a.DepartName,a.ParentDepartCode from tbDeparts a where ParentDepartCode='2' 4 union all 5 select k.DepartCode,k.DepartName,k.ParentDepartCode from tbDeparts k inner join cte c 6 on c.DepartCode = k.ParentDepartCode 7 ) 8 select * from cte