递归用函数、存储过程实现的效果

--測試

 

 

create table ta ( 项目 varchar ( 20), 上级项目 varchar ( 20), num int )

insert ta

select 'A' , '' , 100 union all

select 'B' , 'A' , 200 union all

select 'C' , 'B' , 300 union all

select 'D' , 'B' , 400 union all

select 'E' , 'C' , 500 union all

select 'F' , 'D' , 600 union all

select 'H' , 'E' , 700 union all

select 'G' , 'F' , 800

 

-- 如果要显示上级的项目 :

 

 

 

create function roy_f( @ 项目 varchar ( 20))

returns varchar ( 100)

as

begin

 

declare @ 上级项目 varchar ( 20)

 

select @ 上级项目 = 上级项目 from ta where 项目 = @ 项目

 

return case when @ 上级项目 is null then null

 

            else   isnull ( dbo. roy_f( @ 上级项目 )+ '-' , '' )+ @ 项目 end

end

 

 

 

 

-- 如果显示各级项目的汇总金额 :

 

 

create function roy_f2( @ 项目 varchar ( 20))

returns int

as

begin

    declare @tb table ( 项目 varchar ( 20), 上级项目 varchar ( 20), num int , lev int )

    declare @i int , @sum int

    set @i= 0

    insert @tb select *, @i from ta where 项目 = @ 项目

    while @@rowcount > 0

        begin

            set @i= @i+ 1

            insert @tb

            select a.*, @i

            from ta a, @tb b

            where b. 项目 = a. 上级项目 and b. lev= @i- 1

        end

    select @sum= sum ( num) from @tb

    return @sum

end

 

-- 测试 :

 

select 项目 , 金额 = dbo. roy_f2( 项目 ), 关系 = dbo. roy_f( 项目 ) from ta

 

/*

项目                    金额           关系       

-------------------- ----------- ---------------

A                    3600        A

B                    3500        A-B

C                    1500        A-B-C

D                    1800        A-B-D

E                    1200        A-B-C-E

F                    1400        A-B-D-F

H                    700         A-B-C-E-H

G                    800         A-B-D-F-G

 

（所影响的行数为 8 行）

*/

 

 

-- 用存储过程统计 :

 

create proc roy_p @ 项目 varchar ( 20)

as

begin

declare @i int

set @i= 0

select *, 级数 = @i into #

from ta where 项目 = @ 项目

while @@rowcount > 0

begin

set @i= @i+ 1

insert #

select ta.*, 级数 = @i

from ta, # b

where ta. 上级项目 = b. 项目

and b. 级数 = @i- 1

end

select [sum]= sum ( num) from #

end

 

-- 测试 :

 

exec roy_p 'A'

 

/*

sum       

-----------

3600

 

（所影响的行数为 1 行）

*/

 

 

-- 删除测试 :

 

drop function roy_f, roy_f2

drop proc roy_p

drop table ta