使用Postgresql WITH子句递归地对树的节点求和

（使用Postgresql 9.1）

我在数据库中有一个树结构，我需要对节点的值求和 . 有两点需要注意：

• 并非所有节点都有值 .

• 如果父节点具有值，请忽略子值 .

虽然使用强大的递归WITH子句来递归树是很容易的，但它强制执行这两个破坏我的代码的警告 . 这是我的设置：

CREATE TABLE node (
  id VARCHAR(1) PRIMARY KEY
);

INSERT INTO node VALUES ('A');
INSERT INTO node VALUES ('B');
INSERT INTO node VALUES ('C');
INSERT INTO node VALUES ('D');
INSERT INTO node VALUES ('E');
INSERT INTO node VALUES ('F');
INSERT INTO node VALUES ('G');

CREATE TABLE node_value (
  id VARCHAR(1) PRIMARY KEY,
  value INTEGER
);

INSERT INTO node_value VALUES ('B', 5);
INSERT INTO node_value VALUES ('D', 2);
INSERT INTO node_value VALUES ('E', 0);
INSERT INTO node_value VALUES ('F', 3);
INSERT INTO node_value VALUES ('G', 4);

CREATE TABLE tree (
  parent VARCHAR(1),
  child VARCHAR(1)
);

INSERT INTO tree VALUES ('A', 'B');
INSERT INTO tree VALUES ('B', 'D');
INSERT INTO tree VALUES ('B', 'E');
INSERT INTO tree VALUES ('A', 'C');
INSERT INTO tree VALUES ('C', 'F');
INSERT INTO tree VALUES ('C', 'G');

这给了我以下树（节点和值）：

A
|--B(5)
|  |--D(2)
|  |--E(0)
|
|--C
   |--F(3)
   |--G(4)

根据上述规则，以下是预期的总和值：

A = (5 + 3 + 4) = 12
B = 5
D = 2
E = 0
C = (3 + 4) = 7
F = 3
G = 4

我编写了以下SQL，但是我无法集成递归的UNION和JOIN逻辑来强制执行规则＃1和＃2：

WITH recursive treeSum(root, parent, child, total_value) AS (

  SELECT tree.parent root, tree.parent, tree.child, node_value.value total_value
  FROM tree
  LEFT JOIN node_value ON node_value.id = tree.parent

  UNION

  SELECT treeSum.root, tree.parent, tree.child, node_value.value total_value
  FROM tree
  INNER JOIN treeSum ON treeSum.child = tree.parent
  LEFT JOIN node_value ON node_value.id = tree.parent
)

SELECT root, sum(total_value) FROM treeSum WHERE root = 'A' GROUP BY root

查询为根A返回10，但它应该是12.我知道UNION和/或JOIN逻辑正在抛出这个 . 任何帮助，将不胜感激 .

编辑：澄清一下，A的总和是12而不是14.根据规则，如果节点有值，则 grab 该值并忽略其子节点 . 因为B的值为5，我们忽略D和E. C没有值，所以我们 grab 它的子，因此A = 5（B）3（F）4（G）= 12的总和我知道它很奇怪但是这是要求 . 谢谢 .

编辑2：这些结果将与外部数据集结合，因此我无法在WITH子句中对根进行硬编码 . 例如，我可能需要这样的东西：

SELECT root, SUM(total_value) FROM treeSUM GROUP BY root WHERE root = 'A'

这个树是其中之一，因此意味着有多个根，通过调用代码指定 - 不在递归子句本身内 . 谢谢 .

编辑3：如何在 生产环境 中使用它的一个例子是根将由另一个表指定，所以我不能将根硬编码到递归子句中 . 许多树木可能有许多根源 .

SELECT id, SUM(COALESCE(value,0)) FROM treeSUM 
INNER JOIN roots_to_select rts ON rts.id = treeSUM.id GROUP BY id

解决方案（从koriander的答案中清除）！以下允许由外部源指定根（使用 roots_to_select 或 WHERE 条件：

WITH recursive roots_to_select AS (
  SELECT 'A'::varchar as id
),

treeSum(root, id, value) AS (

select     node.id as root, node.id, node_value.value
from       node
inner join roots_to_select rts on (node.id = rts.id)
left join  node_value          on (node.id = node_value.id)

union

select     treeSum.root, node.id, node_value.value 
from       treeSum
inner join tree       on (treeSum.id = tree.parent)
inner join node       on (tree.child = node.id)
left join  node_value on (node.id    = node_value.id)
where      treeSum.value is null
)

select   root, sum(coalesce(value, 0))
from     treeSum
group by root

输出：12