SQL Server -如何将带有分组小计的列添加到查询结果中
SQL Server -如何将带有分组小计的列添加到查询结果中
提问于 2018-09-05 22:06:55
我有一个数据库表，其中的项被放置在树层次结构中。项有它们的ID和ParentID。每个项目都有一个分配给它的数量，所以当我在我的层次结构中提取数据及其数量时，它看起来如下所示：
| 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在单独的行中拥有整个表(所有层次结构)的总汇总将是很好的。
回答 1
Stack Overflow用户
发布于 2018-09-05 22:30:02
您现有的结果数据使我认为您可能已经在使用某种形式的递归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，但失败了。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52187155
复制相关文章
相似问题
腾讯云开发者
