自引用表与子表

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中实现这个吗？提前感谢。

- Eric

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

- MatBailie

---

谢谢回复，Dem。我使用该查询语句时遇到了两个错误信息：GROUP BY、HAVING 或聚合函数不允许在递归公共表达式“flattend”的递归部分中使用。和外连接不允许在递归公共表达式“flattend”的递归部分中使用。你有什么想法吗？ - Eric

@Eric - 我太久没练习了，甚至都不记得那是这种情况。我已重新编码，使得所有聚合都在 CTE 外完成。 - MatBailie

抱歉回复晚了，昨天我不得不提前离开，今天上午一直在开会。查询非常有效。但是我对CTE和COALESCE函数的理解有些困难。我需要将ParentID列添加到结果中，以便可以在应用程序中重建树形结构，但我一直得到不同的计数。你能再帮我一点吗？再次感谢。 - Eric

我通过连接树表来解决了这个问题。非常感谢。 - Eric

Answer 2

这里是：

假设有这个架构：

​create table #parent (
ID int,
ParentID int,
Name varchar(50) );

create table #child (
ID int,
Sales int );

这个查询是自说明的：

WITH 
  tree AS
(
  SELECT
    id as id_parent,
    id as id
  FROM
    #parent
  UNION ALL
  SELECT
    tree.id_parent as id_parent,
    #parent.id AS id
  FROM
    tree
  INNER JOIN
    #parent    
      ON tree.id = #parent.ParentID
)
SELECT
  #parent.id,
  #parent.name,
  COALESCE(SUM(#child.Sales), 0) AS total
FROM
  #parent
LEFT JOIN
  tree
    ON #parent.ID = tree.id_parent
LEFT JOIN
  #child on tree.id = #child.id
GROUP BY
  #parent.id,
  #parent.name

CTE返回每个员工（#parent）的“叶子”列表，然后查询对这些“叶子”的所有销售额进行求和。您可以运行测试。

已编辑

查询已修复。

- dani herrera

---

谢谢您的回答。我尝试运行查询，但是我注意到根节点的总和不正确。John的总和应该是800而不是400。 - Eric

@Eric，已修复。但要小心，John的总和是600而不是800（John=0，Mike=0，Erin=100+100，Janie=200+200）。 - dani herrera

我的错，谢谢你纠正了。我在这个问题上花费了太多时间，最终得到了我需要的东西。再次感谢。 - Eric

我已经查看了Dems的答案，因为他先回答了这个问题，而且他的方法非常接近你给出的解决方案。如果我做错了，请告诉我正确的方法。非常感谢你的帮助。 - Eric