我有一个名为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所有父子关系
发布评论