对递归表上的树求和 [英] sum a column for trees on a Recursive Tables

问题描述

嗨...
我有一个表(fctTable)，它引用另一个与其自身具有父/子关系的表(refTable).

我需要做的是为fctTable中的每一行获取整个父母树的refTable上的一列总和.

这很难解释，所以我有一个例子...

这是我的两个桌子:

fctTable

桌子>

refTable

Id	refTableId
1	2
2	7
3	4
4	9
5	8

Id	ParentId	Value
1	0	17
2	1	11
3	0	23
4	5	5
5	3	7
7	2	13
8	0	3
9	8	19

我需要解决这个问题:
结果

fctTableId	(refTableIds)	SUM(refTable.Value)
1	2,1	11 + 17 = 28
2	7,2,1	13 + 11 + 17 = 41
3	4,5,3	5 + 7 + 23 = 35
4	9,8	19 + 3 = 22
5	8	3 = 3

我不需要结果中的refTableId列，那只是为了帮助我弄清楚我在做什么....

我可以通过创建类似于Anish M提到的SP的表值函数来做到这一点

Hi...
I have a table (fctTable), which references another table (refTable) that has a parent/child relationship to it''s self.

What i need to do is for each row in the fctTable i need to get the sum of a column on the refTable for whole tree of parents.

this is hard to explain so i have an example...

Here are my two tables:

fctTable

Id	refTableId
1	2
2	7
3	4
4	9
5	8

refTable

Id	ParentId	Value
1	0	17
2	1	11
3	0	23
4	5	5
5	3	7
7	2	13
8	0	3
9	8	19

and i need to get this out:
Result

fctTableId	(refTableIds)	SUM(refTable.Value)
1	2,1	11 + 17 = 28
2	7,2,1	13 + 11 + 17 = 41
3	4,5,3	5 + 7 + 23 = 35
4	9,8	19 + 3 = 22
5	8	3 = 3

I don''t need the refTableId''s column in the results, thats just to help me work out what the heck i''m doing....

I can do it by creating a table valued function similar to the SP mentioned by Anish M here, which gets the tree for a given refTableId, and then calling that from a select statment that chooses the rows in the fctTable i want to use.

but that seems clunky and slow (i may have millions of records in the fctTable to do this for so slow ≡ bad)

I was wondering if any one can think of a better solution... any ideas?

推荐答案

Mark回答了我在SQLServerCentral

Mark replied to with an answer to my post on SQLServerCentral here.

Thanks everyone for taking part!

这篇关于对递归表上的树求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！