我有下表：
Id ParentId Weight
1 1 0
2 1 10
3 2 5
ParentId 引用同一个表的 Id 。如何查询该表，以便将其连接起来，将第三列的累积权重相加？
例如，如果我想知道 Id 2 的累积重量，结果将返回 15 (Id2 + Id3 = 15)，因为项目 3 的父级是 2。如果我想知道项目 3 的累积重量，它将返回返回 5，因为没有记录具有第 3 项的父 id。
本质上，如果我正在查询的记录有一个子项，我想添加数据的子项序列并返回一个结果。
这是否可以一次性完成数据库，或者我是否必须遍历整个记录集才能找到匹配项？
I have the following table:
Id ParentId Weight
1 1 0
2 1 10
3 2 5
ParentId references Id of the same table. How can I query this table so that I join it on itself, adding up the cumulative weight of the third column?
For example, if I wanted to know the cumulative weight of Id 2, the result would return 15 (Id2 + Id3 = 15) as the parent of item 3 is 2. If I wanted to know the cumulative weight of item 3, it would return 5, as no records have a parent id of item 3.
Essentially, if the record I am querying has a child, I want to add the sequence of data's children and return one result.
Is this possible to do in one fell swoop to the database or would I have to loop through the entire record set to find matches?
发布评论
评论（3）
请查看这篇文章。如果您的表不经常更新，您可以稍微修改一下他们的 GenericTree 过程，它会生成所有行的所有路径（并在每次向表中插入记录或更新 ParentId< 时调用它） /code> 列），将此数据存储到一个新表中，然后您可以使用简单查询执行所需的所有任务。就个人而言，我最终得到以下表结构：
即使对于相当大的
my_table，填充该表的数据也不会花费太长时间。Take a look on this article. If your table is not updated frequently, you can modify a little their
GenericTreeprocedure that it generates all paths for all rows (and call it every time you insert record into the table or updateParentIdcolumn), store this data into a new table, and then you can perform all the tasks required using simple queries. Personally, I end up with the following table structure:Populating data for that table doesn't take too long even for a quite large
my_table.最后我发现，mysql 没有内置的方法来执行分层查询，但您始终可以使用诸如邻接列表之类的技术，该技术在 管理 MySQL 中的分层数据，它对另一个表中的层次结构进行编码，并允许您加入该表以检索层次结构中的子树。
Last I looked, mysql didn't have a built-in way of doing hierarchical queries, but you can always use a technique such as the adjacency list, discussed (among other techniques) in Managing Hierarchical Data in MySQL, which encodes the hierarchy in another table and lets you join against that to retrieve subtrees in your hierarchy.
您需要为您的树建立索引。有关执行此操作的一些方法，请参阅管理 MySQL 中的分层数据。
You need to index your tree. See Managing Hierarchical Data in MySQL for some ways to do this.