选择语句以返回父级和无限子级

Question

给出表结构，如下所示：

ID      ParentID      Name
1       NULL          根节点A
2       NULL          另一个根节点
3       1             1的子节点
4       3                1的孙子节点
5       4                   1的曾孙节点
6       1             1的子节点
7       NULL          另一个根节点
8       7             7的子节点

我正在寻找一种优雅的（如果可能的话）解决方案，以便在给定ID = 1时返回表中所有数据的单个Sql语句/函数。

所以我的结果应该是这样的:

ID      ParentID      Name
1       NULL          根节点A
3       1             1的子节点
4       3                1的孙子节点
5       4                   1的曾孙节点
6       1             1的子节点

我在SO上看到过类似的问题，但大多数情况下它们似乎只考虑了一定数量的层级。

这种结构可以是无限的 - 文件夹有子文件夹，其中有许多其他子文件夹。

这是否可行？如果是，我该如何实现？

- Darren Wainwright

---

您可以使用递归CTE来识别父行和子行，就像这里的示例一样：http://stackoverflow.com/questions/22909692/sql-server-cte-parent-child-recursive - Tanner

Answer 1

参考这个答案：

SQL Server CTE Parent Child recursive

以下是使用您的模式的可工作版本：

表创建脚本：

CREATE TABLE YOUR_TABLE
    ([ID] int, [ParentID] int, [Name] varchar(21))
;
    
INSERT INTO YOUR_TABLE
    ([ID], [ParentID], [Name])
VALUES
    (1, NULL, 'A root'),
    (2, NULL, 'Another root'),
    (3, 1, 'Child of 1'),
    (4, 3, 'Grandchild of 1'),
    (5, 4, 'Great grandchild of 1'),
    (6, 1, 'Child of 1'),
    (7, NULL, 'Another root'),
    (8, 7, 'Child of 6')
;

递归公共表达式

DECLARE @ID INT = 1

;WITH ParentChildCTE
AS (
    SELECT ID, ParentId, Name        
    FROM YOUR_TABLE
    WHERE Id = @ID

    UNION ALL

    SELECT T1.ID, T1.ParentId, T1.Name        
    FROM YOUR_TABLE T1
    INNER JOIN ParentChildCTE T ON T.ID = T1.ParentID
    WHERE T1.ParentID IS NOT NULL
    )
SELECT *
FROM ParentChildCTE

重点在于 CTE 的创建部分，其中 UNION ALL 加入结果集，将 ID 与 ParentId 进行连接，这样不会限制层数。

- Tanner

---

谢谢！这正是我想要的 - 通过我的问题作为例子来引导我。有时候，由于SQL脑抽，将其他示例转换起来有点困难 :) - Darren Wainwright

Answer 2

你需要的是一个递归查询。这个微软Technet的例子正好符合你的要求，尽管在这个例子中，我们是针对员工层次结构而不是文件夹进行操作。

从链接页面中可以看到：

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30)  NOT NULL,
LastName  nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

然后

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
        0 AS Level
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        Level + 1
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO

你的结果是

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer       0
1         273        Vice President of Sales       1
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

- Captain Kenpachi