关于树结构统计的sql语句

[Iamalex]

我有一个树结构如下：
A--B--B1
B2
C--C1
D--D1
D2
D3--D31
采用两个表，表一为客户表，字段包括：CustomerId,HighLevelId（上级主管）
另一个表为客户付款记录表，主要字段：PayId,CustomerId,PayMoney
现在要统计每个客户的付款金额，要求上下级关系限制为5级，感觉上很麻烦，根据网上一些文章作了一个递归函数可以实现某个客户（包括他的下级客户）数据的统计，不过没法做到只统计5级关系的数据。各位大虾指点一下，多谢！另外如何做到统计所有客户的数据（上下级关系限制到5级），并且按统计数据的金额倒序排列？

递归函数如下：
CREATE FUNCTION dbo.GetTotalSalary
(
@CustomerId AS INT
)
RETURNS int AS
BEGIN
RETURN (
SELECT SUM(p.PayMoney) FROM Customer c JOIN Payment p
ON c.CustomerId = p.CustomerId
WHERE c.CustomerId = @CustomerId) +
CASE
WHEN EXISTS(SELECT * FROM Customer WHERE HighLevelId = @CustomerId) THEN
( SELECT SUM(dbo.GetTotalSalary(CustomerId)) FROM Customer
WHERE HighLevelId = @CustomerId
)
ELSE 0
END
END

[Iamalex]

To:zlp321002(龙卷风2006)
请问如何计算递归层数？
To:sxycgxj(云中客)
采用你的方法确实可以简化一些操作，不过我的数据结构已经定下来了，再不改变我的数据结构的情况下是否有好的解决办法呢？

谢谢！

[zlp321002]

--你把树的递归层数,计算出来.层数等于5的,应该就是你所要的记录集!

[云中客]

如果修改一下你的数据表结构思路会不会好一些
比如我们建这样一个树形表：

客户编码 客户名称 级数（表示为第几层）。。。。。。
01 A 0
0101 A1 1
0102 A2 1
010101 A11 2
。。。。。。。。。。。。
以此类推
如果你要统计客户A及它以下的所有客户的业务量
可以这样完成：
select sum(业务量） from 客户表 客户编码 like '01%' group by 客户编码
不必要使用递归来完成，而且如果系统中需要树形结构显示时也很方便

不知道楼主是不是需要这个

[Iamalex]

还没有找到好的办法，请各位大虾不吝赐教，多谢!

[being21]

小生，以前没有见过。见个世面！

[Iamalex]

多谢各位帮忙，采用licry01(龙) 的方法，问题解决了。另外，Haiwer(海阔天空）的函数未能通过测试，感觉sxycgxj(云中客) 的数据结构最为简单。再次感谢各位大虾的帮忙。

[昵称被占用了]

这个问题其实不难，先说思路

递归都是可以转化成循环的，先转化成循环
在循环加5层的限定就ok了

[昵称被占用了]

--改后函数，未测试
CREATE FUNCTION dbo.GetTotalSalary
(
@CustomerId AS INT
)
RETURNS int AS
BEGIN

declare @t table (CustomerId int,lv int)
declare @lv int
set @lv=1
insert @t values(@CustomerId,0)

while exists (SELECT * FROM Customer
WHERE HighLevelId in (select CustomerId from @t and lv=@lv-1)
and CustomerId not in (select CustomerId from @t)
) and @lv<=5
begin
insert @t SELECT CustomerId,@lv FROM Customer
WHERE HighLevelId in (select CustomerId from @t and lv=@lv-1)
and CustomerId not in (select CustomerId from @t)
set @lv=@lv+1
end

RETURN (
SELECT SUM(p.PayMoney) FROM @t c JOIN Payment p
ON c.CustomerId = p.CustomerId
WHERE c.CustomerId = @CustomerId
)

END

[云中客]

我觉得一个好的数据库结构会对你以后的工作带来很多好处,如果重新修改数据库结构不会给工作带太多的不便,不如修改一下也好

[licry01]

看你对效率不是很在乎，把你的改了下，用效率低下的游标来处理，同时每次递归的时候把层次也加上。
具体的我没测，你测测看是否符能用

CREATE FUNCTION dbo.GetTotalSalary ( @CustomerId AS INT , @level as smallint)
RETURNS int
AS
BEGIN
declare @high_level_id int , @return_value int , @temp_cusid int , @temp_v int

select @return_value = 0

select @return_value = isnull(sum(PayMoney),0) from Customer where CustomerId = @CustomerId;


declare c1 cursor LOCAL FAST_FORWARD for
(SELECT CustomerId FROM Customer WHERE HighLevelId = @CustomerId)
open c1

fetch c1 into @temp_cusid
while @@fetch_status>=0
begin
if @level<5 --如果到了第五层，不再往下递归，注：第一层的level为1
begin
select @return_value = @return_value + isnull(dbo.GetTotalSalary(@temp_cusid,@level+1),0)
end

FETCH NEXT FROM c1 INTO @temp_cusid
end
close c1
deallocate c1

return @return_value

END