父子层次结构的递归查询.从顶级节点获取后代 [英] Recursive query for parent child hierarchy. Get descendants from a top node

问题描述

我有一张表，该表以一个顶级节点的形式存储父级子级格式的层次结构数据.多个级别，每个父级都有多个孩子.如何编写递归查询以仅选择从特定节点到最后一个子节点的父子行?

I have a table that stores hierarchy data in parent child format with one top node. Multiple levels, and each parent having multiple children. How can I write a recursive query to select only the parent child rows from a particular node down to the last child?

示例表

Parent|child 
1     |2 
1     |3 
2     |4 
2     |5 
3     |6 
3     |7 
6     |8

如何仅从节点3及其所有后代中检索行?

How can I retrieve only rows from node 3 and all its descendants?

推荐答案

如果您的DBMS是SQL Server，则可以使用递归通过通用表表达式(CTE)来实现.我相信这适用于所有2008R2及更高版本.下面的查询将为您提供3的后代的所有父子关系.

If your DBMS is SQL Server you can accomplish this through Common Table Expressions (CTE) using recursion. I believe this works on all versions 2008R2 and above. The below query will give you all the Parent - Child relationships that are descendants of 3.

CREATE TABLE dbo.ParentChildRel
(
    Parent INT
   ,Child INT
)

INSERT INTO dbo.ParentChildRel
VALUES (1,2)
      ,(1,3)
      ,(2,4)
      ,(2,5)
      ,(3,6)
      ,(3,7)
      ,(6,8)
      ,(7,10)

;WITH Hierarchy AS
    (
        SELECT Parent   
              ,Child
        FROM dbo.ParentChildRel
        WHERE Parent = 3
        UNION ALL
        SELECT rel.Parent
              ,rel.Child
        FROM Hierarchy hier
             INNER JOIN dbo.ParentChildRel rel ON hier.Child = rel.Parent
    )
SELECT *
FROM Hierarchy

结果

Parent  Child
3       6
3       7
7       10
6       8

这篇关于父子层次结构的递归查询.从顶级节点获取后代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！