关于SQL汇总的优化算法，欢迎讨论。

Question

假设三层（多一层加多一个update）：update a
 set Amount=(select sum(Amount) from tablename where name like rtrim(a.name)+'-_____')
 from tablename a
where name like '_____-_____'update a
 set Amount=(select sum(Amount) from tablename where name like rtrim(a.name)+'-_____')
 from tablename a
where name like '_____'

Answer 1

Yang_(扬帆破浪):
我正在写，你就答复出来了，快！实在是快！
你的思路和我现在的差不多。如果是N层，则Update N-1次。
但是这种方法，我还觉得有点慢。我试过，如果有5层，1万条记录，肯定超过1分种。而且，如果5个用户并发操作，肯定死锁。

Answer 2

Yang_(扬帆破浪):
我正在写，你就答复出来了，快！实在是快！
你的思路和我现在的差不多。如果是N层，则Update N-1次。
但是这种方法，我还觉得有点慢。我试过，如果有5层，1万条记录，肯定超过1分种。而且，如果5个用户并发操作，肯定死锁。
还有没有更好的?

Answer 3

可以用两句来:
1.先把有子树的AMOUNT设为0
2.再用一句UPDATE

Answer 4

j9988(j9988):
一句行吗？能讲详细些吗？
-------------------------
"再用一句UPDATE"。

Answer 5

我没有说清楚，记录数肯定超过1万条。
-------------------------------------------------------------------
 回复人： Yang_(扬帆破浪) (  ) 信誉：308  2002-09-25 16:48:00 
  记录数如果只有1万条左右，不用特别考虑速度。
  
 

Answer 6

update a set amount=0 from t3 a where exists(select 1 from t3 where ParentID=a.id)update a set amount=(select sum(amount) from t3 where left(name,len(a.name)+1)=a.name+'-') from t3 a where exists(select 1 from t3 where ParentID=a.id)

Answer 7

就是说每次运行时先把有子节点的父节点AMOUNT清0
再让父节点的AMOUNT为所有子孙节点AMOUNT的和

Answer 8

假设 该表名table1， 更新前先复制成另一个表table2。update a
 set Amount=(select sum(Amount) from table2 where left(name,length( rtrim(a.name))）=rtrim(a.name)
 from table1 a思路：某一层次的name的amount是下面层次的amount之和。
      复制另一相同表，是为了避免重复计算。

Answer 9

可以考虑触发器方法，在每次添加、修改、删除记录时自动修改父结点。可以利用ParentID字段提高效率。

Answer 10

多谢大家的讨论，各位的帖子我还没有仔细研究，等一晚上，看看各位的思路中有没有金子可挖！
但是，我觉得可能大家没有仔细看我的提问。请注意：
1、假设Name中的层数是不定的，也就是说如果按照(扬帆破浪)的思路，在UPDATE之前还有一段计算层数的时间。请考虑每次计算层数所消耗的时间，下面是我的代码：
CREATE Function GetItemNumberTierCount
(@ItemNumber  Varchar(25))
RETURNS Tinyint
AS
BEGIN
DECLARE @TierCount Tinyint --存放返回值
DECLARE @Position Tinyint SET @Position=1
SET @TierCount=1
WHILE @Position<=Len(@ItemNumber)
BEGIN
IF Left(@ItemNumber,@Position) Like '%-'
BEGIN
SET @TierCount=@TierCount+1
END
SET @Position=@Position+1
END RETURN @TierCount
END
GOselect GetItemNumberTierCount(max(len(name))) from table12、表中的记录数超过1万条，而且实际应用中还有汇总后列与列之间的计算。
例如：Quantity需要先分层汇总，然后Amount=Quantity*Price。所以我目前的提问只是一个算法模型，所以要求在超过1万条的情况下(内存：1024kb~190mb,tmpdb:20M)，计算时间至少<1Second我目前用的算法和 Yang_(扬帆破浪)最先说的一样，但是速度不太理想。希望大家继续提出宝贵意见！

Answer 11

如果你的name很有规律,计算层数可以：
select max(len(name))/5 from table1

Answer 12

试试这样：
update t3 set amount=t5.amount
from tree t3,(
select t0.id,sum(isnull(t1.amount,0)) as amount
from tree t0,tree t1
where  left(t1.name,len(t0.name))=t0.name and not exists(select parentid from tree t2 where t2.parentid=t1.id)
group by t0.id) t5
where t3.id=t5.id and exists (select parentid from tree t4 where t4.parentid=t3.id)

Answer 13

不过我建议你在表里加几个字段：
fhaschild--是否有子节点
flevel--是第几层
这样在做类似统计时对效率有很大的好处。只是在新增修改时算法会麻烦一点。
如果有这两个字段，上面的语句会简化些，且效率高很多。

Answer 14

上面的第一个回复有问题，现在写了存储过程，你测一下效率：
create proc Pr_ChangeAmount
as
declare @index int
set @index=0
select @index=max(len(name))/5-1 from tbltest
declare @name varchar(30)
while @index>0
begin
set @Name=REPLICATE ('_____-',@Index)
set @Name=left(@Name,len(@Name)-1)
        select @Name
update a
 set Amount=isnull((select sum(Amount) from tbltest where name like rtrim(a.name)+'-_____'),Amount)
 from tbltest a
where name like @name
set @index=@index-1
end
go

Answer 15

to:Yang_(扬帆破浪)
即然用了循环了，干嘛不用，先清零再一句出来？

Answer 16

to: j9988(j9988) 
清零要判断是不是叶子结点，叶子结点不能清，否则就丢是数据了。
我不知道怎么写一条语句！

Answer 17

exists(select 1 from t where ParentID=a.id)
如果没有ParentID=当前ID的，就是末节点，就可清0

Answer 18

关键是我写不出一条update语句！

Answer 19

我试了一下：
create table t3 (ID int,Name varchar(20),Amount decimal(10,4),ParentID int)
insert t3 select 1, '00001', null, Null
union all select 12, '00004-00002' ,null, 10
union all select 13, '00004-00002-00001' ,0.1, 12
union all select 2, '00001-00001' ,60000, 1
union all select 7, '00003-00001-00001' ,1000, 6
union all select 8, '00003-00002' ,100, 5
union all select 9, '00003-00003' ,10, 5
union all select 10, '00004' ,null, Null
union all select 11, '00004-00001' ,1, 10
union all select 14, '00004-00002-00002' ,0.01, 12
union all select 15, '00004-00002-00003' ,0.001, 12
union all select 3, '00001-00002' ,40000, 1
union all select 4, '00002' ,10000, Null
union all select 5, '00003' ,null, Null
union all select 6, '00003-00001' ,null, 5
--上面打乱了update a set amount=0 from t3 a where exists(select 1 from t3 where ParentID=a.id)
--影响5行
update a set amount=(select sum(amount) from t3 where left(name,len(a.name)+1)=a.name+'-') from t3 a where exists(select 1 from t3 where ParentID=a.id)
--影响5行
select * from t3 order by id
：
ID          Name                 Amount       ParentID    
----------- -------------------- ------------ ----------- 
1           00001                100000.0000  NULL
2           00001-00001          60000.0000   1
3           00001-00002          40000.0000   1
4           00002                10000.0000   NULL
5           00003                1110.0000    NULL
6           00003-00001          1000.0000    5
7           00003-00001-00001    1000.0000    6
8           00003-00002          100.0000     5
9           00003-00003          10.0000      5
10          00004                1.1110       NULL
11          00004-00001          1.0000       10
12          00004-00002          .1110        10
13          00004-00002-00001    .1000        12
14          00004-00002-00002    .0100        12
15          00004-00002-00003    .0010        12（所影响的行数为 15 行）没有错啊？

Answer 20

哦，明白了：create proc Pr_ChangeAmount
asupdate a
set Amount=0
from tbltest a
where exists (
select 1 from tbltest where ParentID=a.id
)update a
set Amount=(select sum(Amount) from tbltest where name like rtrim(a.name)+'-%')
from tbltest a
where exists (
select 1 from tbltest where ParentID=a.id
)
go

Answer 21

如果name字段是varchar的，加索引(name),(ParentID),再改成如下，速度应该可以了：create proc Pr_ChangeAmount
asupdate a
set Amount=0
from tbltest a
where exists (
select 1 from tbltest where ParentID=a.id
)update a
set Amount=(select sum(Amount) from tbltest where name like a.name+'-%')
from tbltest a
where exists (
select 1 from tbltest where ParentID=a.id
)
go

Answer 22

这样就很快了
不过我还是觉得用触了器。每改变一次只修改其中一支。
语句跟上面一样，多加一个WHERE条件罢了。
这样如果记录多了用户多了，可能会好点。

Answer 23

早先，我自己没有解放思想，差点漏掉了j9988的好办法。
To j9988：
为什么要先将父节点清零？不清零也可以的啊？如果是为了消除因聚合函数中出现空值而产生的警告，是不是可以用SET ANSI_WARNINGS OFF/ON ，这样更快？
To Yang_：
“触发器有分散统计的功能”具体是指什么意思？

Answer 24

在我提问前，Name字段中的值并不是现在这样，而是“1”、“1-01”、“1-Z”等等(所以没有用"select max(len(name))/5 from table1")。
为了便于讨论，我构造了该字段的值，其思想是来自于VB中TreeView的FullPathCode。在讨论中才发现，Name字段在汇总统计时竟然有意想不到的功效。
所以现在看来，处理类似问题，这张表应该为ID、PathCode、Name、Amount、ParentID。再问j8899:
假设表中只有ID、Amount、ParentID，能否一句Update？我想了很久，没想出！

Answer 25

特别鸣谢
Yang_(扬帆破浪)  44分    你第一个回答，而且实际做了不少测试。可惜你比j9988慢了一步...
j9988(j9988)     51分    特别的爱给特别的你，因为你最先解决了问题!
icevi(按钮工厂)  5分     中肯的建议，值得吸取，谢谢！-----------------------------------------------
--先问一问，这样给分，大家有没有意见？
-----------------------------------------------

Answer 26

不清零会造成重复计算
触发器是在插入或者修改时计算，不必集中统计。
表中只有ID、Amount、ParentID，可以先把字节点id放到临时表，再update
怎么给分自己作主，不必看人家意见。

Answer 27

我说的是一句Update,好像不行吧？
---------------------------
表中只有ID、Amount、ParentID，可以先把字节点id放到临时表，再update

Answer 28

错别字：表中只有ID、Amount、ParentID，可以先把子节点id放到临时表，再update