MySQL所有父子关系 [英] MySQL all parent-child relationships

问题描述

我有一个名为table的表.它有一个名为id且类型为INT(11)的字段，代表该行的标识符，它具有其他字段，但我认为它们与该问题无关.

I have a table named table. It has a field named id with type INT(11) that stands for an identifier of the row, it has other fields but I don't think they are relevant for this problem.

我还有一个名为table_children的表.它具有一个名为parent且类型为INT(11)的字段，该字段将table.id引用为外键.它具有另一个名为child且类型为INT(11)的字段，该字段也将table.id称为外键.下表描述了table行到table行的父子关系.

I have another table named table_children. It has a field named parent with type INT(11) that refers to table.id as a foreign key. It has another field named child with type INT(11) that also refers to table.id as a foreign key. This table describes table row to table row parent-child relationships.

这是一个可能的设置.

table   table_children
id      parent child
0       0      1
1       1      2
2       1      3
3       3      4
4

如何在最少数量的请求中获得0所有后代的id?答案是1，2，3，4.

How can I get the id's of all the descendents of 0 in a minimum number of requests? The answer here would be 1, 2, 3, 4.

谢谢您的帮助.

推荐答案

使用MySQL，最简单的方法是将 all 路径存储在树中，创建

With MySQL, the easiest way I do this is to store all paths in the tree, creating a transitive closure.

table_children
parent child
0      0
1      1
2      2
3      3
4      4
0      1
0      2
0      3
0      4
1      2
1      3
1      4
3      4

现在您可以通过以下方式查询它:

Now you can query it thus:

SELECT t.*
FROM table_children c
JOIN table t ON c.child = t.id
WHERE c.parent = 0;

另请参阅:

• 使用SQL和PHP的分层数据模型
• SQL反模式:避免数据库编程的弊端

• What is the most efficient/elegant way to parse a flat table into a tree?
• Models for Hierarchical Data with SQL and PHP
• SQL Antipatterns: Avoiding the Pitfalls of Database Programming

这篇关于MySQL所有父子关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！