MySQL所有父子关系

编程入门行业动态更新时间:2024-10-26 10:36:25

本文介绍了MySQL所有父子关系的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我有一个名为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;