高手求救(SQL)

[zhaokeke2004]

表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(存储过程,游标,函数...)来实现?

[子陌红尘]

create table BOM(Account varchar(10),Sub int,Parent varchar(10),Value int)
insert into BOM values('P0010', 0,'P0210',20) 
insert into BOM values('P0070', 0,'P0210',40) 
insert into BOM values('P0210',20,'P0290', 0) 
insert into BOM values('P0230', 0,'P0290',60) 
insert into BOM values('P0270', 0,NULL   ,55) 
insert into BOM values('P0290',40,NULL   , 0)
go

create function f_isChild(@Account1 varchar(10),@Account2 varchar(10))
returns int
as
begin
    declare @t table(Account varchar(10),Sub int,Parent varchar(10),Value int)
    
    insert into @t select * from BOM where Account=@Account1
    
    while @@rowcount<>0 
    begin
        insert into @t
        select
            a.* 
        from
            BOM a,@t b 
        where
            a.Parent=b.Account and not exists(select 1 from @t where Account=a.Account)
    end
    
    if exists(select 1 from @t where Account=@Account2)
        return 1
    return 0
end
go

select 
    a.Account,
    Value=sum(case when dbo.f_isChild(a.Account,b.Account)=1 then b.Sub+b.Value else 0 end) 
from 
    BOM a,BOM b
group by
    a.Account
go

/*
Account    Value       
---------- ----------- 
P0010      20
P0070      40
P0210      80
P0230      60
P0270      55
P0290      180
*/


drop function f_ischild
drop table BOM
go

[-狙击手-]

晚上回家写，SF

[子陌红尘]

优化一下自定义函数的执行效率：

create table BOM(Account varchar(10),Sub int,Parent varchar(10),Value int)
insert into BOM values('P0010', 0,'P0210',20) 
insert into BOM values('P0070', 0,'P0210',40) 
insert into BOM values('P0210',20,'P0290', 0) 
insert into BOM values('P0230', 0,'P0290',60) 
insert into BOM values('P0270', 0,NULL   ,55) 
insert into BOM values('P0290',40,NULL   , 0)
go

create function f_isChild(@Account1 varchar(10),@Account2 varchar(10))
returns int
as
begin
    declare @t table(Account varchar(10),Sub int,Parent varchar(10),Value int)
    
    insert into @t select * from BOM where Account=@Account1
    
    while @@rowcount<>0 
    begin
        if exists(select 1 from @t where Account=@Account2)
            return 1

        insert into @t
        select
            a.* 
        from
            BOM a,@t b 
        where
            a.Parent=b.Account and not exists(select 1 from @t where Account=a.Account)
    end
    
    return 0
end
go

select 
    a.Account,
    Value=sum(case when dbo.f_isChild(a.Account,b.Account)=1 then b.Sub+b.Value else 0 end) 
from 
    BOM a,BOM b
group by
    a.Account
go

/*
Account    Value       
---------- ----------- 
P0010      20
P0070      40
P0210      80
P0230      60
P0270      55
P0290      180
*/


drop function f_ischild
drop table BOM
go

[子陌红尘]

有点意思，临下班冒出来的几个问题都跟递归相关......

[-狙击手-]

是的，刚想贴BOM，一看你已经回了

[kaikai_kk]

看看,学习

[wzy_love_sly]

[Quote=引用 6 楼 kaikai_kk 的回复:]
看看,学习
[/Quote]

[zhaokeke2004]

To: libin_ftsafe

最后的结果不对,应该是:
Account Value
---------- -----------
P0010 20
P0070 40
P0210 60
P0230 60
P0270 55
P0290 120

你的结果是:
Account Value
---------- -----------
P0010 20
P0070 40
P0210 80
P0230 60
P0270 55
P0290 180

[zhaokeke2004]

把b.Sub+b.Value改成b.Value就OK了,谢谢大家.

[pt1314917]

接分。`

[flairsky]

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*/

符合要求？

[honghuali]

路过！