查询以获取带有子记录的父记录，然后是mysql中的下一个父子记录

我在这里提出的解决方案使用了物化路径的概念。以下是使用样本数据的实例化路径的示例。我希望它可以帮助您理解物化路径的概念：

+----+--------------------------+----------+------------------+| ID |Name| ParentID | MaterializedPath |+----+--------------------------+----------+------------------+|  1 | Parent 1      |        0 | 1     ||  2 | Parent 2      |        0 | 2     ||  4 | Parent 2 Child 1         |        2 | 2.4   ||  6 | Parent 2 Child 1 Child 1 |        4 | 2.4.6 ||  7 | Parent 2 Child 1 Child 2 |        4 | 2.4.7 ||  3 | Parent 1 Child 1         |        1 | 1.3   ||  5 | Parent 1 Child 1 Child   |        3 | 1.3.5 |+----+--------------------------+----------+------------------+

每个节点

N

N

5

1

3

5

5

1.3.5

巧合的是，您要查找的订单可以通过实现路径来实现。

在前面的示例中，实例化路径是buit连接字符串，但是出于多种原因，我更喜欢二进制连接。

要构建物化路径，您需要以下递归CTE：

CREATE TABLE Tree(    ID int NOT NULL ConSTRAINT PK_Tree PRIMARY KEY,     Name nvarchar(250) NOT NULL,    ParentID int NOT NULL,)INSERT INTO Tree(ID, Name, ParentID) VALUES(1, 'Parent 1', 0),(2, 'Parent 2', 0),(3, 'Parent 1 Child 1', 1),(4, 'Parent 2 Child 1', 2),(5, 'Parent 1 Child 1 Child', 3),(6, 'Parent 2 Child 1 Child 1', 4),(7, 'Parent 2 Child 1 Child 2', 4)GOWITH T AS(    SELECt        N.ID, N.Name, N.ParentID, CAST(N.ID AS varbinary(512)) AS MaterializedPath    FROM        Tree N    WHERe        N.ParentID = 0    UNIOn ALL    SELECt        N.ID, N.Name, N.ParentID, CAST( T.MaterializedPath + CAST(N.ID AS binary(4)) AS varbinary(512) ) AS MaterializedPath    FROM        Tree N INNER JOIN T ON N.ParentID = T.ID)SELECt *FROM TORDER BY T.MaterializedPath

结果：

+----+--------------------------+----------+----------------------------+| ID |Name| ParentID |      MaterializedPath      |+----+--------------------------+----------+----------------------------+|  1 | Parent 1      |        0 | 0x00000001      ||  3 | Parent 1 Child 1         |        1 | 0x0000000100000003         ||  5 | Parent 1 Child 1 Child   |        3 | 0x000000010000000300000005 ||  2 | Parent 2      |        0 | 0x00000002      ||  4 | Parent 2 Child 1         |        2 | 0x0000000200000004         ||  6 | Parent 2 Child 1 Child 1 |        4 | 0x000000020000000400000006 ||  7 | Parent 2 Child 1 Child 2 |        4 | 0x000000020000000400000007 |+----+--------------------------+----------+----------------------------+

上面的递归CTE从根节点开始。计算根节点的物化路径非常简单，它是节点本身的ID。在下一次迭代中，CTE将根节点与其子节点连接在一起。子节点

CN

PN

CN