我有一个树连接codeD在MySQL数据库中刃：

I have a tree encoded in a MySQL database as edges:

CREATE TABLE items (
    num INT,
    tot INT,
    PRIMARY KEY (num)
    );
CREATE TABLE tree (
    orig INT,
    term INT
    FOREIGN KEY (orig,term) REFERENCES items (num,num)
    )

有关树中的每个叶子， items.tot 是某些人设定。对于内部节点， items.tot 需要将之和它的孩子们。运行以下查询反复将产生所期望的结果。

For each leaf in the tree, items.tot is set by someone. For interior nodes, items.tot needs to be the sum of it's children. Running the following query repeatedly would generate the desired result.

UPDATE items SET tot = (
    SELECT SUM(b.tot) FROM
        tree JOIN items AS b
        ON tree.term = b.num 
        WHERE tree.orig=items.num)
    WHERE EXISTS 
        (SELECT * FROM tree WHERE orig=items.num)

（注意，这实际上是行不通但那是题外话）

(note this actually doesn't work but that's beside the point)

假设数据库存在和不变量已经满足。

Assume that the database exists and the invariant are already satisfied.

现在的问题是：

什么是更新数据库，同时保持这一要求的最实用的方法是什么？更新可能移动节点周围或改变 TOT 的叶节点的值。它可以假定叶节点将保持为叶节点，内部节点会留作为内部节点和整个事情将保持作为一个适当的树

What is the most practical way to update the DB while maintaining this requirement? Updates may move nodes around or alter the value of tot on leaf nodes. It can be assumed that leaf nodes will stay as leaf nodes, interior nodes will stay as interior nodes and the whole thing will remain as a proper tree.

有些想法，我有：

完全失效，任何更新后，重新计算所有（嗯...没有） 设置中的项目表中的触发器来更新更新任何行的父 这将是递归（更新触发更新，触发更新，...） 在不工作时，MySQL不能更新开锣触发表 Full Invalidation, after any update, recompute everything (Um... No) Set a trigger on the items table to update the parent of any row that is updated This would be recursive (updates trigger updates, trigger updates, ...) Doesn't work, MySQL can't update the table that kicked off the trigger 这将是迭代（您可以通过安排一个项目，处理它计划的更多资料） 什么踢这一关？信托客户code得到它的权利？ 一个优点是，如果更新被正确排序更少总和需要是计算机。但是，排序是在它自己和并发症。

这是理想的解决方案推广到其他聚集不变量

An ideal solution would generalize to other "aggregating invariants"

FWIW我知道这是有点过分，但我这样做是为了好玩（FUN：动词，寻找不可能通过这样做： - ）

FWIW I know this is "a bit overboard", but I'm doing this for fun (Fun: verb, Finding the impossible by doing it. :-)

推荐答案

您遇到的问题是清楚的，递归SQL。你需要得到父母的父母......叶和更新它的总（或者减去旧的和添加新的，或重新计算）。需要某种形式的识别符看的树结构，并抓住所有的节点的孩子和父母的列表/路径到叶更新。

The problem you are having is clear, recursion in SQL. You need to get the parent of the parent... of the leaf and updates it's total (either subtracting the old and adding the new, or recomputing). You need some form of identifier to see the structure of the tree, and grab all of a nodes children and a list of the parents/path to a leaf to update.

此方法添加常数空间（2列到表中 - 但你只需要一个表，否则你可以做更高版本的连接）。我研究了一下结构前一段时间所用使用左和右的列（显然不是这些名字）的分层格式，由pre序遍历和后序遍历，分别--don计算别担心，这些并不需要每次都重新计算。

This method adds constant space (2 columns to your table --but you only need one table, else you can do a join later). I played around with a structure awhile ago that used a hierarchical format using 'left' and 'right' columns (obviously not those names), calculated by a pre-order traversal and a post-order traversal, respectively --don't worry these don't need to be recalculated every time.

我就让你看看一个网页。使用这种方法MySQL的的，而不是继续这个讨论的情况下，你不喜欢这种方法，作为一个答案。但是，如果你喜欢它，后/编辑，我会花一些时间和澄清。

I'll let you take a look at a page using this method in mysql instead of continuing this discussion in case you don't like this method as an answer. But if you like it, post/edit and I'll take some time and clarify.