选择所有层次结构及以下的SQL服务器。

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

我很感激您对此提供任何帮助或想法。

- JDBennett

---

预期结果会是什么？ - Felix Pamittan

我更新了问题以展示期望的结果。 - JDBennett

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);

在线演示

- Felix Pamittan

---

这太完美了！只是出于好奇 - 这个程序在处理大量记录时表现如何？例如，假设我有50K+的记录，并且ID是公司（顶级）父级。它能承受得住吗？ - JDBennett

通常情况下，递归CTE在大表上的扩展性不佳。像所有东西一样，需要进行测试！ - Felix Pamittan

Answer 2

假设您正在使用相当现代的SQL Server版本，您可以使用hierarchyid数据类型并稍加努力。首先，进行设置：

alter table [dbo].[yourTable] add [path] hierarchyid null;

接下来，我们将填充新列：

with cte as (
   select *, cast(concat('/', ID, '/') as varchar(max)) as [path]
   from [dbo].[yourTable]
   where [ParentID] is null

   union all

   select child.*, 
      cast(concat(parent.path, child.ID, '/') as varchar(max)) as [path]
   from [dbo].[yourTable] as child
   join cte as parent
      on child.ParentID = parent.ID
)
update t
set path = c.path
from [dbo].[yourTable] as t
join cte as c
   on t.ID = c.ID;

这只是一个标准的递归表达式，其中包含一个表示层次结构的计算列。那就是难点所在。现在，您的过程可能看起来像这样：

create procedure dbo.GetPositions ( @id int ) as
begin
   declare @h hierarchyid
   set @h = (select Path from [dbo].[yourTable] where ID = @id);

   select ID, ParentID, Name
   from [dbo].[yourTable]
   where Path.IsDescendentOf(@h) = 1;
end

所以，总的来说，使用层次结构标识(hierarchyid)只是为了存储给定行的谱系，这样在选择时就不必实时计算。

- Ben Thul