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

I have a database that stores Cost information for an organization, by a division break-down and time-phased by year. The cost structure involves a parent-child relationship; the users can specify the cost values on any level in the structure, the only restriction is that all the values at higher levels in the hierarchy are computed as the sum of the children nodes, if any child node has a value; parent node values that result as sum of children nodes are not stored in the database.

我有一个数据库,存储组织的成本信息,按部门细分和按年分期。成本结构涉及亲子关系;用户可以在结构中的任何级别指定成本值,唯一的限制是层次结构中较高级别的所有值都计算为子节点的总和,如果任何子节点具有值;父节点值作为子节点的总和而未存储在数据库中。

I need a query which will recursively compute the values of the parents based on their children and for children with no values zero will be set (T-SQL, SQL 2008R2)

我需要一个查询,它将根据子项递归计算父项的值,并且对于没有值为零的子项将被设置(T-SQL,SQL 2008R2)

[SQL Fiddle] MS SQL Server 2008 Schema Setup:

[SQL Fiddle] MS SQL Server 2008架构设置:

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);

Given the structure above and the sample data, this is how things would look like:

鉴于上面的结构和示例数据,这就是事情的样子:

    |        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 |

2 个解决方案

#1

This should give a correct result:

这应该给出正确的结果:

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

Here is the fiddle link: http://sqlfiddle.com/#!3/cd98d/22/0

这是小提琴链接:http://sqlfiddle.com/#!3 / cd98d / 22/0

Note the left join between the two DirectReport part, for keeping also the departments without costs.

注意两个DirectReport部分之间的左连接,以便保持部门免费。

#2

-1

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

#1