如何使用递归实现完整层级排名结构 附SQL实现示例

我们可以通过递归CTE方案生成完整的层级排名结构，以下是全流程可运行的实现代码： #

完整实现代码 #

CREATE DATABASE TASKPERFORMANCE1

USE TASKPERFORMANCE1

DROP TABLE Students

CREATE TABLE Students 
(
     ID VARCHAR(99), 
     Name VARCHAR(99), 
     LeaderID VARCHAR(99)
)

INSERT INTO Students (ID, Name, LeaderID) 
VALUES (100, 'Baldo', null),
       (101, 'Cardo', 101),
       (102, 'Pedro', 101),
       (103, 'Luningning', 101),
       (104, 'Paolo', 102),
       (105, 'Roberto', 102),
       (106, 'Narciso', 102),
       (107, 'Jopay', 102),
       (108, 'Bernardo', 103),
       (109, 'Policarpio', 103),
       (110, 'Sisa', 103),
       (111, 'Reginaldo', 104)
 
;WITH RankingStructure (LeaderID,ID,Name,Ranking) AS
(
    SELECT LeaderID, ID, Name, 0 AS Ranking
    FROM Students
    WHERE LeaderID IS NULL

    UNION ALL
 
    SELECT S.LeaderID, S.ID, S.Name, R.Ranking + 1
    FROM Students S
    INNER JOIN RankingStructure R ON R.ID = S.LeaderID
)
SELECT LeaderID, ID, Name, Ranking
FROM RankingStructure

逻辑说明 #

• 第一步创建测试数据库并切换到该库，准备独立测试环境
• 创建Students表存储人员层级关系，LeaderID字段存储当前人员的上级ID
• 插入测试层级数据，覆盖多阶上下级关联场景
• 递归CTE的实现逻辑：
  • 锚点查询：筛选没有上级（LeaderID为NULL）的顶层节点，设置初始排名为0
  • 递归关联：每次关联上一层级的计算结果，子节点的排名自动在父节点排名基础上加1，向下迭代计算所有节点的层级排名
• 直接查询递归CTE的结果即可得到所有节点的完整层级排名

内容的提问来源于stack exchange，提问作者William Rowell Gimena2à2ĎĖ3