查找树结构中的所有成员 [英] Find all members in a tree structure

问题描述

我继承了这种格式的树型表

I have inherited a tree type table in this format

StatementAreaId | ParentStatementAreaId | SubjectId | Description
-----------------------------------------------------------------
1               | 0                     | 100       | Reading
2               | 0                     | 110       | Maths
3               | 2                     | 0         | Number
4               | 2                     | 0         | Shape
5               | 3                     | 0         | Addition
6               | 3                     | 0         | Subtraction

我想找到最终父主题所在的所有 StatementAreaId，比如数学(即 SubjectId=110).例如，如果 SubjectId 是 Maths，我会在树中得到一个 StatementAreaIds 列表:

I want to find all the StatementAreaIds where the ultimate parent subject is, say maths (i.e. SubjectId=110). For instance if the SubjectId was Maths I'd get a list of StatementAreaIds in the tree:

StatementAreaId
---------------
2
3
4
5
6

如果有帮助，树的最大深度为 3.

The tree has a maximum of a depth of 3 if that helps.

谢谢

推荐答案

递归 CTE 来救援:

Recursive CTE to the rescue:

创建并填充示例表:(请在您以后的问题中保存我们这一步)

Create and populate sample table: (Please save us this step in your future questions)

DECLARE @T AS TABLE
(
    StatementAreaId int,
    ParentStatementAreaId int, 
    SubjectId int,
    Description varchar(20)
)

INSERT INTO @T VALUES
(1               , 0                     , 100       , 'Reading'),
(2               , 0                     , 110       , 'Maths'),
(3               , 2                     , 0         , 'Number'),
(4               , 2                     , 0         , 'Shape'),
(5               , 3                     , 0         , 'Addition'),
(6               , 3                     , 0         , 'Subtraction')

查询:

;WITH CTE AS 
(
    SELECT StatementAreaId, ParentStatementAreaId
    FROM @T 
    WHERE SubjectId = 110

    UNION ALL
    SELECT t1.StatementAreaId, t1.ParentStatementAreaId
    FROM @T t1 
    INNER JOIN CTE ON t1.ParentStatementAreaId = CTE.StatementAreaId
)

SELECT StatementAreaId
FROM CTE 

结果:

StatementAreaId
2
3
4
5
6

这篇关于查找树结构中的所有成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！