高手求救SQL

Question

请参考：
http://topic.csdn.net/u/20080409/17/c6941061-8c1c-4734-8dc4-d372a1073d59.html表A Account Sub Parent Value 
P0010    0   P0210   20 
P0070    0   P0210   40 
P0210   20   P0290    0 
P0230    0   P0290   60 
P0270    0           55 
P0290   40            0 要得到: 表B 
Account Value 
P0010     20 
P0070     40 
P0210     60 
P0230     60 
P0270     55 
P0290    120 说明,所有Sub不是0的记录,他们的Value要重新计算,方法是: 
如:Account为P0210,需要所有Parent是P0210的记录value加起来,P0290要求所有parent是P0290的记录value加起来,但是P0210的parent也是P0290,它本身Sub值也不为0,所以还要展开P0210的所有记录,其他所有Sub为0的记录,VALUE值保持不变 怎么用SQL(存储过程,游标,函数...)来实现?在原来的提问中，有两个人提出了解决方法:
1.子陌红尘: 写了一个f_isChild的函数,的确可以解决问题，但是我现在碰到一个表有388条记录(记录数不多,但是有很多层,我举的例子中只有两层,而且记录数又很少,所以用f_isChild很快)，用f_isChild函数的话，10分钟过后都没数据出来,效率很低!2.flairsky：这个WITH AS应该是ORACLE里面的语法巴?我现在用的是SQL SERVER 2005.
求各位大侠有没有更加好的办法来解决!!!!!!!!!!!!!!!!!

Answer 1

我现在用的是SQL SERVER 2005. 
？
用CTE 

Answer 2

--試試看，沒測試
Create table test(account varchar(10), sub int,parent varchar(10), value int)
insert into test select 'P0010', 0, 'P0210', 20
insert into test select 'P0070', 0, 'P0210', 40
insert into test select 'P0210',20, 'P0290', 0
insert into test select 'P0230', 0, 'P0290', 60
insert into test select 'P0270', 0, '', 55
insert into test select 'P0290', 40, '', 0GOselect * into #test from testwhile @@rowcount>0
begin
update #test
set value=value+(select sum(value) from #test  a where parent=#test.account)
     , sub=0
where sub>0
and not exists(select 1 from #test b where parent=#test.account and sub>0)
endselect account,value  from #test
/*account    value       
---------- ----------- 
P0010      20
P0070      40
P0210      60
P0230      60
P0270      55
P0290      120
*/
GOdrop table #test,test

Answer 3

2.flairsky：这个WITH AS应该是ORACLE里面的语法巴?我现在用的是SQL SERVER 2005. 
--------------
如果你使用的是SQL 2005,
那么这个WITH AS是可以使用的.
这个本来就是SQL 2005的语法.
create table #t(Account varchar(10),Sub int,Parent varchar(10),Value int)
insert into #t values('P0010', 0,'P0210',20) 
insert into #t values('P0070', 0,'P0210',40) 
insert into #t values('P0210',20,'P0290', 0) 
insert into #t values('P0230', 0,'P0290',60) 
insert into #t values('P0270', 0,NULL   ,55) 
insert into #t values('P0290',40,NULL   , 0)select identity(int,1,1) as id,tt.parent  into   #temp   from  (select distinct parent from #t where parent is not null) as ttcreate table #f (Account varchar(10),Value int)insert into #f
select Account,sum(Value)from #t where account not in (select parent from #t where parent is not null)group by account
declare @beginnum int
declare @endnum int
declare @P varchar(100)
set @beginnum=1
set @endnum = (select count(*)from #temp)
while @beginnum<=@endnum
begin
set @p=(select parent from #temp where id=@beginnum)
--set @beginnum=@beginnum+1
--print @p
--end
;WITH
t AS(
    -- 定位点成员
    SELECT * FROM #t
    where account=@p
    UNION ALL
    -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
    SELECT A.*
    FROM #t A, t B
    WHERE A.parent = B.account
)insert into #f 
select @p,sum(value) from tset @beginnum=@beginnum+1
endselect * from #f order by accountdrop table #f
drop table #t
drop table #temp
GO
/*P0010    20
P0070    40
P0210    60
P0230    60
P0270    55
P0290    120*/

Answer 4

if not object_id('A') is null
drop table A
Go
Create table A([Account] nvarchar(5),[Sub] int,[Parent] nvarchar(5),[Value] int)
Insert A
select N'P0010',0,N'P0210',20 union all
select N'P0070',0,N'P0210',40 union all
select N'P0210',20,N'P0290',0 union all
select N'P0230',0,N'P0290',60 union all
select N'P0270',0,null,55 union all
select N'P0290',40,null,0
Go;with CTE as
(select *,[Account] as Name from A where [Sub]<>0 and [Value] is not null
union all
select a.*,c.Name from Cte c join A on c.[Account]=a.[Parent])
select 
a.[Account],[Value]=isnull(b.[Value],a.[Value])
from 
a
left join
(select Name as [Account],sum([Value])[Value] from CTE group by Name) b on a.[Account]=b.[Account]
option(MAXRECURSION  0)
(6 個資料列受到影響)
Account Value
------- -----------
P0010   20
P0070   40
P0210   60
P0230   60
P0270   55
P0290   120(6 個資料列受到影響)

Answer 5

if not object_id('A') is null
drop table A
Go
Create table A([Account] nvarchar(5),[Sub] int,[Parent] nvarchar(5),[Value] int)
Insert A
select N'P0010',0,N'P0210',20 union all
select N'P0070',0,N'P0210',40 union all
select N'P0210',20,N'P0290',0 union all
select N'P0230',0,N'P0290',60 union all
select N'P0270',0,null,55 union all
select N'P0290',40,null,0
Gocreate function F_Value(@Account nvarchar(5))
returns int
as
begin
declare @Value int
select @Value=[Value] from A where [Account]=@Account and [Sub]=0; 
if @Value is not null
return @Value
;with C as
(select * from A where [Account]=@Account
union all
select a.* from C join A on C.[Account]=a.[Parent])
select @Value=sum([Value])  from C
return @Value
end goselect
[Account],
[Value]=dbo.F_Value([Account])
from 
AAccount Value
------- -----------
P0010   20
P0070   40
P0210   60
P0230   60
P0270   55
P0290   120(6 個資料列受到影響)

Answer 6

while @@rowcount>0
begin
update #test
set value=value+(select sum(value) from #test  a where parent=#test.account)
     , sub=0
where sub>0
and not exists(select 1 from #test b where parent=#test.account and sub>0)
end
学习

Answer 7

2.flairsky：这个WITH AS应该是ORACLE里面的语法巴?我现在用的是SQL SERVER 2005. 我和你一样，sql2005