高手求救SQL

[zhaokeke2004]

请参考：
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.


求各位大侠有没有更加好的办法来解决!!!!!!!!!!!!!!!!!

[水族杰纶]

学习~~~
期待高人帮助你~

[中国风]

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

[hyde100]

友情帮顶顶

[playwarcraft]

--試試看，沒測試
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, '', 0

GO

select * into #test from test

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

select account,value  from #test
/*

account    value       
---------- ----------- 
P0010      20
P0070      40
P0210      60
P0230      60
P0270      55
P0290      120
*/
GO

drop table #test,test

[sdxiong]

怎么不说清楚数据库的版本？

[hery2002]

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 tt

create 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 t

set @beginnum=@beginnum+1
end

select * from #f order by account

drop table #f
drop table #t
drop table #temp
GO
/*P0010    20
P0070    40
P0210    60
P0230    60
P0270    55
P0290    120*/

[中国风]

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 個資料列受到影響)

[中国风]

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

create 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 

go

select
	[Account],
	[Value]=dbo.F_Value([Account])
from 
	A

Account Value
------- -----------
P0010   20
P0070   40
P0210   60
P0230   60
P0270   55
P0290   120

(6 個資料列受到影響)

[zhiguo2008]

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


学习

[flairsky]

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

我和你一样，sql2005