我有一个数据库表，其中包含放置在树层次结构中的项目。项目具有其 ID 和父 ID。每个项目都分配了一个金额，因此当我在层次结构及其金额上提取数据时，它看起来像这样：
| ID | Name | ParentID | Level | Amount |
| 1 | ITEM 1 | NULL | 0 | 100 |
| 3 | - Item 1-1 | 1 | 1 | 100 |
| 7 | - - Item 1-1-1 | 3 | 2 | 100 |
| 8 | - - Item 1-1-2 | 3 | 2 | 100 |
| 4 | - Item 1-2 | 1 | 1 | 100 |
| 9 | - - Item 1-2-1 | 4 | 2 | 100 |
| 10 | - - Item 1-2-2 | 4 | 2 | 100 |
| 2 | ITEM 2 | NULL | 0 | 200 |
| 5 | - Item 2-1 | 2 | 1 | 200 |
| 11 | - - Item 2-1-1 | 5 | 2 | 200 |
| 12 | - - Item 2-1-2 | 5 | 2 | 200 |
| 6 | - Item 2-2 | 2 | 1 | 200 |
| 13 | - - Item 2-2-1 | 6 | 2 | 200 |
| 14 | - - Item 2-2-2 | 6 | 2 | 200 |
假设这是一个新表。我需要从中选择数据，并按每个层次结构级别的金额计算组小计，如下所示：
| ID | Name | ParentID | Level | Amount | Group rollup |
| 1 | ITEM 1 | NULL | 0 | 100 | 700 |
| 3 | - Item 1-1 | 1 | 1 | 100 | 300 |
| 7 | - - Item 1-1-1 | 3 | 2 | 100 | 100 |
| 8 | - - Item 1-1-2 | 3 | 2 | 100 | 100 |
| 4 | - Item 1-2 | 1 | 1 | 100 | 300 |
| 9 | - - Item 1-2-1 | 4 | 2 | 100 | 100 |
| 10 | - - Item 1-2-2 | 4 | 2 | 100 | 100 |
| 2 | ITEM 2 | NULL | 0 | 200 | 1400 |
| 5 | - Item 2-1 | 2 | 1 | 200 | 600 |
| 11 | - - Item 2-1-1 | 5 | 2 | 200 | 200 |
| 12 | - - Item 2-1-2 | 5 | 2 | 200 | 200 |
| 6 | - Item 2-2 | 2 | 1 | 200 | 600 |
| 13 | - - Item 2-2-1 | 6 | 2 | 200 | 200 |
| 14 | - - Item 2-2-2 | 6 | 2 | 200 | 200 |
请帮我实现这个目标。
现在，我正在使用此查询来选择数据。但是，它不会正确排序项目，也不会执行汇总。您可以将其调整为如上所示对它们进行排序并计算组汇总吗？
WITH cte AS
(SELECT ID,
Name,
ParentID,
0 AS LEVEL,
Amount
FROM MyTable
WHERE ParentID IS NULL
UNION ALL
SELECT t.ID,
t.Name,
t.ParentID,
LEVEL+1,
t.Amount
FROM cte
JOIN MyTable t ON cte.ID = t.ParentID)
SELECT d1.*
FROM cte d1
最好在单独的行中对整个表(所有层次结构(进行总计汇总。
您现有的结果数据让我认为您可能已经在使用某种形式的递归 CTE 来构建诸如Level和Name之类的东西。如果是这样，这可能可以同时调整为计算hier。
但如果没有，我们使用递归 CTE 来构建我们的hier列，然后在计算总和时使用IsDescendantOf：
declare @t table(ID int, Name varchar(30), ParentID int, Level int, Amount int)
insert into @t(ID ,Name , ParentID , Level , Amount) values
(1 ,'ITEM 1 ',NULL, 0 ,100 ),
(3 ,' - Item 1-1 ', 1, 1 ,100 ),
(7 ,' - - Item 1-1-1', 3, 2 ,100 ),
(8 ,' - - Item 1-1-2', 3, 2 ,100 ),
(4 ,' - Item 1-2 ', 1, 1 ,100 ),
(9 ,' - - Item 1-2-1', 4, 2 ,100 ),
(10 ,' - - Item 1-2-2', 4, 2 ,100 ),
(2 ,'ITEM 2 ',NULL, 0 ,200 ),
(5 ,' - Item 2-1 ', 2, 1 ,200 ),
(11 ,' - - Item 2-1-1', 5, 2 ,200 ),
(12 ,' - - Item 2-1-2', 5, 2 ,200 ),
(6 ,' - Item 2-2 ', 2, 1 ,200 ),
(13 ,' - - Item 2-2-1', 6, 2 ,200 ),
(14 ,' - - Item 2-2-2', 6, 2 ,200 )
;With hierarchy as (
select ID, Name, '/' + CONVERT(varchar(max),ID) + '/' as hier,Amount
from @t
where ParentID is null
union all
select t.ID,t.Name, hier + CONVERT(varchar(max),t.ID) + '/',t.Amount
from
hierarchy h
inner join
@t t
on
t.ParentID = h.ID
), Typed as (
select ID,Name,Amount,CONVERT(hierarchyid,hier) as hier
from hierarchy
)
select
*
from
Typed t1
cross apply
(select SUM(Amount) as GroupTotal from Typed t2
where t2.hier.IsDescendantOf(t1.hier) = 1) u
order by hier
结果：
ID Name Amount hier GroupTotal
----------- ------------------------------ ----------- ------------- -----------
1 ITEM 1 100 0x58 700
3 - Item 1-1 100 0x5BC0 300
7 - - Item 1-1-1 100 0x5BE7 100
8 - - Item 1-1-2 100 0x5BE880 100
4 - Item 1-2 100 0x5C20 300
9 - - Item 1-2-1 100 0x5C34C0 100
10 - - Item 1-2-2 100 0x5C3540 100
2 ITEM 2 200 0x68 1400
5 - Item 2-1 200 0x6C60 600
11 - - Item 2-1-1 200 0x6C75C0 200
12 - - Item 2-1-2 200 0x6C7640 200
6 - Item 2-2 200 0x6CA0 600
13 - - Item 2-2-1 200 0x6CB6C0 200
14 - - Item 2-2-2 200 0x6CB740 200
在此阶段，您还可以决定需要更改数据模型，以便直接使用hierarchyid类型而不是父/子类型对层次结构进行建模。这取决于此数据必须满足哪些其他用例。
这里的问题是，大多数分层查询最终倾向于自上而下探索层次结构，但您想要的数据只能自下而上轻松获得。我确实试图弄清楚是否有一个聪明的GROUP BY或ORDER BY表达式(后者用于窗口函数(也可以使用IsDescendantOf但失败了。