sql - 怎样才能执行所有的Ranking STRUCTURE。使用递归

Question

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

标签： sqlsql-serverrecursive-query