sql递归查找以及给有标识的用户分层级

WITH D(UserId,ReferralUserId,DistributorGradeId,LevelId,IsEnterprisePartner,UpEnterpriseUserId,UpTwoEnterpriseUserId,UpThreeEnterpriseUserId) AS(
SELECT UserId,ReferralUserId,DistributorGradeId,0 as LevelId,IsEnterprisePartner,(case IsEnterprisePartner when 1 then UserId else 0 end) UpEnterpriseUserId,0 as UpTwoEnterpriseUserId,0 as UpThreeEnterpriseUserId FROM aspnet_Distributors
WHERE UserId=38546
UNION ALL
SELECT a.UserId,a.ReferralUserId,a.DistributorGradeId,D.LevelId + 1 as LevelId,a.IsEnterprisePartner,
(case a.IsEnterprisePartner when 1 then a.UserId else d.UpEnterpriseUserId end) as UpEnterpriseUserId,
(case a.IsEnterprisePartner when 1 then d.UpEnterpriseUserId else d.UpTwoEnterpriseUserId end) as UpTwoEnterpriseUserId,(case a.IsEnterprisePartner when 1 then d.UpTwoEnterpriseUserId else d.UpThreeEnterpriseUserId end) as UpThreeEnterpriseUserId
FROM aspnet_Distributors a INNER JOIN D ON a.ReferralUserId=D.UserID
)
select D.* from D

– IsEnterprisePartner(是否是合伙人), UpEnterpriseUserId（上级合伙人Id）,UpTwoEnterpriseUserId（上上级合伙人Id）,UpThreeEnterpriseUserId上上上级合伙人Id） –自己标识是1就是自己，不是就是上级，再不是就是上级的上级