使用SQL server hierarchyid如何在祖先级别聚合叶节点值

基于以下输出

我想汇总祖先级别的数据，即城市/州/国家。 例如：在上面的例子中，我们有澳大利亚国家和两个州（VIC和NSW）。 VIC国家有两个城市（墨尔本和本迪戈），新州没有城市。 查询的结果应该显示完全相同的记录数，但在祖先级别显示累计金额。 说墨尔本应该显示800，VIC应该显示1300，澳大利亚2500显示如下：

使用递归CTE使用hierarchyid的GetAncestor函数可以做到这一点吗？

CREATE TABLE #Test（EmployeeNode hierarchyid，EmployeeName varchar（50），Location varchar（50），Amount decimal）

ALTER TABLE #Test ADD [Level] as EmployeeNode.GetLevel（）

INSERT INTO #Test（EmployeeNode，Location，EmployeeName，Amount）VALUES（CAST（'/ 0 /'AS hierarchyid），'Australia'，NULL，NULL）

INSERT INTO #Test（EmployeeNode，Location，EmployeeName，Amount）VALUES（CAST（'/ 0/1 /'AS hierarchyid），'VIC'，NULL，NULL）

INSERT INTO #Test（EmployeeNode，Location，EmployeeName，Amount）VALUES（CAST（'/ 0/1/1 /'AS hierarchyid），'Melbourne'，NULL，NULL）

INSERT INTO #Test（EmployeeNode，Location，EmployeeName，Amount）VALUES（CAST（'/ 0/1/1/1 /'AS hierarchyid），NULL，'Mary'，300.00）

INSERT INTO #Test（EmployeeNode，Location，EmployeeName，Amount）VALUES（CAST（'/ 0/1/1/2 /'AS hierarchyid），NULL，'Timothy'，500.00）

INSERT INTO #Test（EmployeeNode，Location，EmployeeName，Amount）VALUES（CAST（'/ 0/1/2 /'AS hierarchyid），'Bendigo'，NULL，NULL）

INSERT INTO #Test（EmployeeNode，Location，EmployeeName，Amount）VALUES（CAST（'/ 0/1/2/1 /'AS hierarchyid），NULL，'Paul'，500.00）

INSERT INTO #Test（EmployeeNode，Location，EmployeeName，Amount）VALUES（CAST（'/ 0/2 /'AS hierarchyid），'NSW'，NULL，NULL）

INSERT INTO #Test（EmployeeNode，Location，EmployeeName，Amount）VALUES（CAST（'/ 0/2/1 /'AS hierarchyid），'NSW'，'Peter'，1200.00）

SELECT t.EmployeeNode.ToString（），t。* FROM #Test t

---

declare @Test as Table ( EmployeeNode HierarchyId, EmployeeName VarChar(50), Location VarChar(50), Amount Decimal );

insert into @Test ( EmployeeNode, Location, EmployeeName, Amount ) values
  ( Cast( '/0/' as HierarchyId ), 'Australia', NULL, NULL ),
  ( Cast( '/0/1/' as HierarchyId ), 'VIC', NULL, NULL ),
  ( Cast( '/0/1/1/' as HierarchyId ), 'Melbourne', NULL, NULL ),
  ( Cast( '/0/1/1/1/' as HierarchyId ), NULL, 'Mary', 300.00 ),
  ( Cast( '/0/1/1/2/' as HierarchyId ), NULL, 'Timothy', 500.00 ),
  ( Cast( '/0/1/2/' as HierarchyId ), 'Bendigo', NULL, NULL ),
  ( Cast( '/0/1/2/1/' as HierarchyId ), NULL, 'Paul', 500.00 ),
  ( Cast( '/0/2/' as HierarchyId ), 'NSW', NULL, NULL ),
  ( Cast( '/0/2/1/' as HierarchyId ), 'NSW', 'Peter', 1200.00 );

select EmployeeNode.ToString(), *, EmployeeNode.GetLevel() as [Level],
  ( select Sum( Amount )
      from @Test where EmployeeNode.IsDescendantOf( P.EmployeeNode ) = 1 ) as [Total]
  from @Test as P;

链接地址: http://www.djcxy.com/p/60227.html

上一篇: Using SQL server hierarchyid how to aggregate leaf node values at ancestor level

下一篇: How to prune dead branches in a hierarchical data structure