在层次结构SQL中查找所有子级

一尘不染

在层次结构SQL中查找所有子级

sql

我有一个像这样的数据库层次结构

LHID | location     | parent
------------------------------
1    | Trim         | NULL
2    | Trim - South | Trim
3    | South-1      | Trim - South

我需要做的是选择Trim-
South的所有子代。它非常深，大约有100个子位置，我已经找到了递归的方法，但是没有运气来复制它们。我知道递归选择所有子级的逻辑，因为我已经用其他语言完成了类似的过程，但是从来没有使用过SQL，因此我很难掌握它的确切必要语法。有任何想法吗？

阅读 135

2021-03-17

共1个答案

一尘不染

您可以使用递归CTE：

WITH CTE 
AS(
  SELECT 1 AS relationLevel, child.*
  FROM dbo.TableName child
  WHERE child.parent = 'Trim - South'

  UNION ALL

  SELECT relationLevel+1, parent.*
  FROM CTE nextOne
  INNER JOIN  dbo.TableName parent ON parent.parent = nextOne.location
)
SELECT * FROM CTE ORDER BY relationLevel

结果：

RELATIONLEVEL   LHID    LOCATION    PARENT
1                3      South-1     Trim - South
1                4      South-2     Trim - South
2                4      South-2-1   South-2

**[DEMO](http://sqlfiddle.com/#!3/102ac/2/0)**

也许您想从父母到孩子旅行，然后使用以下命令：

WITH CTE 
AS(
  SELECT 1 AS relationLevel, parent.*
  FROM dbo.TableName parent
  WHERE parent.location = 'Trim - South'

  UNION ALL

  SELECT relationLevel + 1, child.*
  FROM CTE nextOne
  INNER JOIN  dbo.TableName child ON child.parent = nextOne.location
)   
SELECT * FROM CTE ORDER BY relationLevel

结果：

RELATIONLEVEL   LHID    LOCATION    PARENT
1               2    Trim - South   Trim
2               3    South-1        Trim - South
2               4    South-2        Trim - South
3               4    South-2-1      South-2

[**DEMO**](http://sqlfiddle.com/#!3/102ac/3/0)

2021-03-17