从无限层次结构中选择子项

Question

我有一个像这样的SQL表格：

ID    | ParentID
1     | null
2     | null
3     | null
4     | 1
5     | 4
6     | 5
7     | 6
8     | 7
9     | 8

现在，正如您所见，一个子父关系被维护着。我想要一个查询来选择给定ID的"所有级别"的子代。

假设我输入ID = 1，则结果应该是：

ID    | ParentID
1     | null
4     | 1
5     | 4
6     | 5
7     | 6
8     | 7
9     | 8

所有直接的子元素，以及它们在任意级别上的子元素，都应该出现。

在MS SQL Server中是否可以实现这一点？我使用的是MS-SQL Server 2012。

- yogi

---

https://dev59.com/SHVC5IYBdhLWcg3wsTjv - Sajith

Answer 1

这是一个带有额外字段名称的示例，但使用CTE递归很简单：

DECLARE @ID int

SET @ID = 1;

WITH CTE_Table_1
(
  ID,
  Name,
  ParentID,
  TreeLevel
)
AS(
 SELECT 
  ID,
  Name,
  ParentID,
  0 AS TreeLevel
 FROM Table_1
 WHERE ID = @ID

UNION ALL

 SELECT 
  T.ID,
  T.Name,
  T.ParentID,
  TreeLevel + 1
 FROM Table_1 T
 INNER JOIN CTE_Table_1 ON CTE_Table_1.ID = T.ParentID
)

SELECT * FROM CTE_Table_1

- András Ottó

Answer 2

尝试这个，很好用：http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=101053

-- Structure
create table dbo.MyPeople (Id int, Name varchar(30), ParentId int)

-- Data
insert dbo.MyPeople
          select 1, 'P1', null
union all select 2, 'P2', null
union all select 3, 'P1C1', 1
union all select 4, 'P1C2', 1
union all select 5, 'P2C1', 2
union all select 6, 'P1C2C1', 4
union all select 7, 'P1C1C1', 3
union all select 8, 'P1C1C1C1', 7
union all select 9, 'P2C1C1', 5
union all select 10, 'P1C3', 1
go

-- Function
create function dbo.AncestorPath(@Id int) returns varchar(100) as
begin
    declare @Path varchar(100)
    while 0 = 0
    begin
        select @Path = cast(Id as varchar(5)) + isnull('/' + @Path, ''), @Id = ParentId
        from dbo.MyPeople where Id = @Id

        if @@rowcount = 0 break
    end

    return @Path
end
go

-- Calculation
select * from (
    select *, dbo.AncestorPath(Id) as AncestorPath from dbo.MyPeople) a
where '/' + AncestorPath + '/' like '%/1/%'

或者

尝试像这样的递归过程

ALTER PROCEDURE dbo.GetChildren
@ParentId int

AS

SET NOCOUNT ON

SELECT * 
FROM MainTable 
WHERE ChildId IN 
(
SELECT ParentId 
FROM f_GetChildren(@ParentId)
UNION
SELECT ChildId
FROM f_GetChildren(@ParentId)
)
ORDER BY ParentId

SET NOCOUNT OFF
RETURN

- Pranay Rana

Answer 3

从SQL Server 2005开始，通用表达式已被添加到SQL Server的T-SQL中，可帮助您进行此类层次查询。这正是您正在寻找的！

- Vikdor

---

是的，那可能会对我有所帮助，但我不知道如何操作，请提供一些示例。 - yogi

Andras已经为此提供了CTE，我认为你可以开始了。 - Vikdor