CRM 2011 组织关系树查询

 declare @username nvarchar(20),@id nvarchar(50) set @username='李帅'; select @id=businessunitid from systemuser where fullname=@username; select name ,parentbusinessunitidname from businessunit where businessunitid=@id;

WITH StepCTE AS (

SELECT

businessunitid,

parentbusinessunitid, parentbusinessunitidname,

name,

1 as Lev

FROM

Businessunit

WHERE

businessunitid =@id

UNION ALL

SELECT

T.businessunitid,

T.parentbusinessunitid, t.parentbusinessunitidname, T.name,

CTE.Lev + 1

FROM

Businessunit T INNER JOIN StepCTE CTE

ON T.parentbusinessunitid = CTE.businessunitid

)

SELECT

businessunitid, parentbusinessunitid, name,parentbusinessunitidname, Lev

FROM StepCTE order by Lev