Mysql - 如何仅使用祖先 id 获取所有后代(甚至孙子等..)？

Question

我确实想从我的树中获取所有后代(包括孙子)ID，我只需输入父ID。我目前使用闭包表作为我的方法。我有这个表用于存储父级和子级 id:

CREATE TABLE `treepaths` (
`ancestor` int(11) NOT NULL,
`descendant` int(11) NOT NULL,
 PRIMARY KEY (`ancestor`,`descendant`),
KEY `FK_Descendant_idx` (`descendant`),
CONSTRAINT `FK_Ancestor` FOREIGN KEY (`ancestor`) REFERENCES `organization` 
(`organization_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_Descendant` FOREIGN KEY (`descendant`) REFERENCES 
`organization` (`organization_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

这是我插入数据的位置:

CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateChild`(
Ancestor int,
Descendant int,

ParentID int,
ChildID int
)
BEGIN
INSERT INTO treepaths
VALUES (Ancestor, Descendant);
SELECT tree.ancestor, ChildID FROM treepaths tree
WHERE tree.descendant = ParentID
UNION ALL SELECT ChildID, ChildID; 
END

这是我读取数据的地方:

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetOrganizationDescendant`(
Ancestor int
)
BEGIN
SELECT org.* FROM organization org 
JOIN treepaths tree ON org.organization_id = tree.descendant
WHERE tree.ancestor = Ancestor;
END

目前，它仅检索父级的直系子级，而不是孙级。有什么办法可以做到这一点吗？

更新我使用 this 修改我的表和邻接列表模型作为引用。现在，我想将父级包含在表中。我该怎么做呢？这是样本fiddle上面的引用资料

Answer 1

我建议您切换到层次结构/树的嵌套集模型 - 使您尝试运行并且毫无疑问将来想要运行的查询类型变得很多更容易

• Wikipedia Nested-Set-Model
• MySQL example