sql - 带有子表的自引用表

Question

我有一个自引用表，内容如下:

Self-referencing parent table
ID  ParentID    Name
---------------------
1               John
2   1           Mike
3   2           Erin
4   1           Janie
5               Eric
6   5           Peter

树层次结构应如下所示

约翰

麦克风

艾琳

珍妮

埃里克

彼得

还有一个存储父表叶子的子表，如下所示:

ID  Sales
3   100
3   100
4   200
4   200
6   300
6   300
6   300

我正在尝试将叶节点的总和向上汇总到层次结构，以便它返回为 ..

ID  Name    Sum
1   John    800
2   Mike    200
3   Erin    200
4   Janie   400
5   Eric    900
6   Peter   900

任何想法如何在 sql 2008 中实现这一点？提前致谢。

Answer 1

编辑 所有聚合都移出了 CTE

WITH
  tree AS
(
  SELECT
    id                AS root_id,
    name              AS root_name,
    id                AS leaf_id
  FROM
    yourTreeTable

  UNION ALL

  SELECT
    tree.root_id      AS root_id,
    tree.name         AS root_name,
    yourTreeTable.id  AS leaf_id
  FROM
    tree
  INNER JOIN
    yourTreeTable
      ON tree.leaf_id = yourTreeTable.ParentID
)
SELECT
  tree.root_id,
  tree.root_name,
  COALESCE(SUM(yourScoresTable.score), 0) AS total
FROM
  tree
LEFT JOIN
  yourScoresTable
    ON yourScoresTable.ID = tree.leafID
GROUP BY
  tree.root_id,
  tree.root_name