选择所有层次结构级别以及下面的SQL Server

Question

我在这方面遇到了困难。我已经看到了一些示例，这些示例说明了如何从给定父级的自引用表中获取所有子级记录，甚至如何获取子级记录的父级。

我想做的是返回一条记录，并给所有子记录指定ID。

为了说明这一点-我有一个公司层次结构。在哪里：

#Role        Level#
--------------------
Corporate         0
Region            1
District          2
Rep               3

我需要的是一个过程（1）弄清记录的级别，以及（2）检索该记录和所有子记录。

作为区域的想法可以看到一个区域中的所有地区和代表，而地区可以看到他们的代表。代表只能看到自己。

我有桌子：

ID            ParentId           Name
-------------------------------------------------------
1             Null               Corporate HQ
2             1                  South Region
3             1                  North Region
4             1                  East Region
5             1                  West Region
6             3                  Chicago District
7             3                  Milwaukee District
8             3                  Minneapolis District
9             6                  Gold Coast Dealer
10            6                  Blue Island Dealer

我该怎么做呢：

CREATE PROCEDURE GetPositions
    @id int
AS
BEGIN
    --What is the most efficient way to do this--
END
GO

例如，@ id = 3的预期结果，我想返回：

3, 6, 7, 8, 9, 10

我将不胜感激，对此有任何帮助或想法。

Answer 1

您可以通过递归CTE来做到这一点：

DECLARE @id INT = 3;

WITH rCTE AS(
    SELECT *, 0 AS Level FROM tbl WHERE Id = @id
    UNION ALL
    SELECT t.*, r.Level + 1 AS Level
    FROM tbl t
    INNER JOIN rCTE r
        ON t.ParentId = r.ID
)
SELECT * FROM rCTE OPTION(MAXRECURSION 0);

[**ONLINE DEMO**](https://data.stackexchange.com/stackoverflow/query/476511)