使用SQL Server从父ID获取嵌套子对象

CREATE TABLE Object (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    ParentID INT
);

ID  Name    ParentID
1   Object1 NULL
2   Object2 1
3   Object3 1
4   Object4 2
5   Object5 2

WITH RecursiveQuery AS (
    SELECT ID, Name, ParentID
    FROM Object
    WHERE ID = @ParentID -- 指定父ID
    UNION ALL
    SELECT o.ID, o.Name, o.ParentID
    FROM Object o
    INNER JOIN RecursiveQuery r ON o.ParentID = r.ID
)
SELECT ID, Name, ParentID
FROM RecursiveQuery;

ID  Name    ParentID
1   Object1 NULL
2   Object2 1
3   Object3 1
4   Object4 2
5   Object5 2