递归求和在父子层次结构中的 T-SQL

Question

我有一个数据库，存储了一个组织的成本信息，按照部门分解并按年份分段。成本结构涉及到父子关系；用户可以在任何层级上指定成本值，唯一的限制是如果任何子节点具有值，则层次结构中较高层级的所有值都将计算为子节点的总和；作为子节点总和结果的父节点值不会存储在数据库中。

我需要一个查询，它将基于其子项递归计算父项的值，并且对于没有值的子项将设置为零（T-SQL，SQL 2008R2）。

[SQL Fiddle] MS SQL Server 2008 Schema Setup:

CREATE TABLE CostStructureNodes (
  Id INT NOT NULL PRIMARY KEY,
  Name NVARCHAR(250) NOT NULL,
  ParentNodeId INT,
  FOREIGN KEY(ParentNodeId) REFERENCES CostStructureNodes(Id)
); 

CREATE TABLE Years (
  Year INT NOT NULL PRIMARY KEY
);

CREATE TABLE CostsPerYear (
  NodeId INT NOT NULL,
  Year INT NOT NULL,
  Value DECIMAL(18,6) NOT NULL,
  PRIMARY KEY(NodeId, Year),
  FOREIGN KEY(NodeId) REFERENCES CostStructureNodes(Id),
  FOREIGN KEY(Year) REFERENCES Years(Year)
);

INSERT INTO CostStructureNodes VALUES ('1', 'Total Costs', NULL);
INSERT INTO CostStructureNodes VALUES ('2', 'R&D', 1);
INSERT INTO CostStructureNodes VALUES ('3', 'Legal', 1);
INSERT INTO CostStructureNodes VALUES ('4', 'HR', 1);
INSERT INTO CostStructureNodes VALUES ('5', 'IT', 1);
INSERT INTO CostStructureNodes VALUES ('6', 'Software', 5);
INSERT INTO CostStructureNodes VALUES ('7', 'Hardware', 5);

INSERT INTO Years VALUES (2010);
INSERT INTO Years VALUES (2011);
INSERT INTO Years VALUES (2012);

INSERT INTO CostsPerYear VALUES (1, 2010, 100000);
INSERT INTO CostsPerYear VALUES (2, 2011, 50000);
INSERT INTO CostsPerYear VALUES (5, 2011, 20000);
INSERT INTO CostsPerYear VALUES (6, 2012, 22000);
INSERT INTO CostsPerYear VALUES (7, 2012, 13000);
INSERT INTO CostsPerYear VALUES (2, 2012, 76000);

考虑到上述结构和示例数据，事情将是这个样子：

    |        NAME | YEAR |  VALUE |
    -------------------------------
    | Total Costs | 2010 | 100000 |
    |         R&D | 2010 |      0 |
    |          IT | 2010 |      0 |
    |    Software | 2010 |      0 |
    |    Hardware | 2010 |      0 |
    |          HR | 2010 |      0 |
    | Total Costs | 2011 |  70000 |
    |         R&D | 2011 |  50000 |
    |          IT | 2011 |  20000 |
    |    Software | 2011 |      0 |
    |    Hardware | 2011 |      0 |
    |          HR | 2011 |      0 |
    | Total Costs | 2012 | 111000 |
    |         R&D | 2012 |  76000 |
    |          IT | 2012 |  35000 |
    |    Software | 2012 |  22000 |
    |    Hardware | 2012 |  13000 |
    |          HR | 2012 |      0 |

- kjv

---

请重新检查所请求的输出。考虑到HR的ID为4，并且您在示例模式的最后一行中为2012年的HR插入了75000，我很难理解为什么所请求的输出中HR在2012年的值为0。 - souplex

我修复了示例数据插入。 - kjv

Answer 1

这应该会得到正确的结果：

with DirectReport (ParentNodeId, Id, Name, Level, Struc, year)
as
(
  -- anchor
  select a.ParentNodeId, a.Id, a.Name, 0 as Level, cast(':' + cast(a.Id as varchar) + ':' as varchar (100))  as Struc, y.year
  from CostStructureNodes a, Years y
  where a.ParentNodeId is null
  union all
  -- recursive
  Select a.ParentNodeId, a.Id, a.Name, Level +1, cast(d.Struc + cast(a.Id as varchar)+ ':'  as varchar(100)) as Struc, d.year
  from CostStructureNodes a
    join DirectReport d on d.Id = a.ParentNodeId
  )

Select d.ParentNodeId, d.year, d.Id, d.Name, d.level, d.Struc,-- dd.Struc, 
sum(case when d.Struc = SUBSTRING(dd.Struc, 1, len(d.Struc))then c.Value else 0 end) as TotCost
from DirectReport d
  left join DirectReport dd on d.year = dd.year 
  join CostsPerYear c on c.Year = dd.year and c.NodeId = dd.Id
 group by d.ParentNodeId, d.year, d.Id, d.Name, d.level, d.Struc
order by  d.year, d.id

这是 fiddle 链接：http://sqlfiddle.com/#!3/cd98d/22/0。

请注意两个 DirectReport 部分之间的左连接，以保留没有成本的部门。

- momobo

Answer 2

WITH DirectReport (ParentNodeId, Id, Name, LEVEL, Struc)
AS
(
-- anchor
SELECT a.ParentNodeId, a.Id, a.Name, 0 AS LEVEL, cast(':' + cast(a.Id AS varchar) + ':' AS varchar (100))  AS Struc
FROM CostStructureNodes a
WHERE a.ParentNodeId IS NULL
UNION ALL
-- recursive
SELECT a.ParentNodeId, a.Id, a.Name, LEVEL +1, cast(d.Struc + cast(a.Id AS varchar)+ ':'  AS varchar(100)) AS Struc
FROM CostStructureNodes a
  JOIN DirectReport d ON d.Id = a.ParentNodeId
)

SELECT d.ParentNodeId, d.Id, d.Name, d.level, d.Struc,
sum(CASE WHEN d.Struc = SUBSTRING(dd.Struc, 1, len(d.Struc))THEN c.Value ELSE 0 END) AS    TotCost
FROM DirectReport d,DirectReport dd
JOIN CostsPerYear c ON c.NodeId = dd.Id
GROUP BY d.ParentNodeId,d.Id, d.Name, d.level, d.Struc
ORDER BY  d.id

- kamal