T-SQL中的递归查询

WITH businessunitTree
        AS ( SELECT 
                    Name,BusinessUnitId,ParentBusinessUnitId,ParentBusinessUnitIdName,0 as lev
            FROM     dbo.BusinessUnit
            WHERE    BusinessUnitId = '4DEAF471-8EA1-E611-80C5-005056BD289F'
            UNION ALL
            SELECT   fa.Name,fa.BusinessUnitId,fa.ParentBusinessUnitId,fa.ParentBusinessUnitIdName,son.lev+1
            FROM     dbo.BusinessUnit fa
                    INNER JOIN businessunitTree son ON son.ParentBusinessUnitId = fa.BusinessUnitId
            )
     SELECT Name,BusinessUnitId,ParentBusinessUnitId,ParentBusinessUnitIdName,lev FROM businessunitTree WHERE lev = (SELECT MAX(businessunitTree.lev)-1 FROM businessunitTree)
 此查询相当于把as中上一个查询结果当作临时表busiessunitTree，然后在查询中把子表（busiessunitTree）和原表（Businessunit）进行结合查询查询到上一级信息，然后再与其进行Union连接，依次递归，最终递归返回原等级的所有上级信息。