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

最新推荐文章于 2025-06-23 13:46:24 发布

原创最新推荐文章于 2025-06-23 13:46:24 发布 · 500 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

日常总结专栏收录该内容

20 篇文章

订阅专栏

本文介绍了一种使用SQL的WITH语句进行递归查询的方法，以解析分销商的层级结构，包括直接上级合伙人和间接上级合伙人的ID。通过递归联合的方式，实现了对分销商层级的深度遍历。

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就是自己，不是就是上级，再不是就是上级的上级