happyflystone,今天测试出来有问题。

[iky453]

create function f_getParent(@ID int,@atopname int)
returns varchar(40)
as
begin
declare @ret varchar(40)

while exists(select 1 from Agencies where PKId=@ID and ATopNode>@atopname)
begin
select @ID=b.PKId,@ret=','+rtrim(isnull(b.PKId,0))
from
Agencies a,Agencies b
where
a.PKId=@ID and b.PKId=a.ATopNode
end

set @ret=stuff(@ret,1,1,'')
return @ret
end
go


create proc proc_test
@ATopNode int
as
begin
declare @s varchar(8000)
set @s = ''
select @s = @s + ',['+IName+']= max(case when FType = '+ ltrim(PKId) + ' then FAccount else 0 end) '
from items
--print @s
set @s = ('select 机构名称 = cast(a.AName as varchar)'+@s+'
,sum(FAccount) as 合计 from (select * from Agencies where ATopNode = '+ltrim(@ATopNode )+') a
left join Householders b on a.PKId = dbo.f_getParent(b.AgenciesId,'+ltrim(@ATopNode)+')
left join Financial c on b.Pkid = c.HouseholdersId group by a.AName')

exec( @s)
end

go

测试

declare @ATopNode int
set @ATopNode = 1（或者2或者3都没问题）
但是测试到4，5就无法统计出来了，全部是0
exec proc_test @ATopNode

http://topic.csdn.net/u/20080105/04/f0b88745-4e43-41b2-a84e-344bcbd02ddf.html（你上次帮我解的）

[-狙击手-]

嘿嘿，看康熙王朝了，
加个判定

CREATE       TABLE       Agencies   
(   
PKId       INT       PRIMARY       KEY,   
AName       NVARCHAR(200),       --       机构名称   
AOrganId       Int       ,       --       对应"AgencyOrgan"表的PKId   
ATopNode       INT           --父节点   

)   

--       人员表   
CREATE       TABLE       Householders   
(   
PKId       INT      PRIMARY       KEY,   
AgenciesId       INT       ,       --关联"Agencies"表的PKId   
HName       NVARCHAR(100)       NOT       NULL,   
HCard       NVARCHAR(50)       ,       --身份证   
HPhone       NVARCHAR(50)       ,   
HAddress       NVARCHAR(200)       

)   
--       财务类型表   
CREATE       TABLE       Items   
(   
PKId       INT     PRIMARY       KEY,   
IName       NVARCHAR(100)       --       类型名称   
)   
--       财务表       
CREATE       TABLE       Financial   
(   
PKId       INT      PRIMARY       KEY,       
FAccount       FLOAT       ,       --金额   
FDate       DATETIME,       --       发放时间   
FReceive       BIT       ,--       是否领取   
FType       INT       ,       --       财务类型,关联"Items"表   
HouseholdersId       INT       NOT       NULL       --       关联"Householders"表   
)   

set nocount on
insert Items select 1             ,'学杂费' 
insert Items select 2             ,'书本费' 
insert Items select 3             ,'服装费' 


insert Financial select 1 , 200,'2007-1-1',0,                   1   ,                1 
insert Financial select 2 , 100,'2007-1-8',1 ,                  2    ,               2 
insert Financial select 3 , 200,'2007-8-4',1  ,                 3     ,              3 
insert Financial select 4 , 300,'2007-10-2',1        ,           2       ,            4 
--insert Financial select 5 , 300,'2007-10-2',1        ,           2       ,            5 

insert Agencies select 1,'院校',                   1   ,            0 
insert Agencies select 2,'四川大学',           2   ,                        1 
insert Agencies select 3,'华西大学',           2    ,                       1 
insert Agencies select 4,'川大计科院',       3       ,                    2 
insert Agencies select 5,'川大法学院',       3        ,                   2 
insert Agencies select 6,'华大计科院',       3         ,                  3 
insert Agencies select 7,'川大计科系',       4          ,                 4 
insert Agencies select 8,'川大法学系',       4          ,                 5 
insert Agencies select 9,'华大计科系',       4            ,               6 

insert Householders select 1,7,'张明','122211','0000 ','地址A'   
insert Householders select 2,8,'李达','222222','0000','地址B'   
insert Householders select 3,7,'杨峰','1111','0000','地址C' 
insert Householders select 4,9,'罗明','22222','0000','地址D' 
--insert Householders select 5,4,'罗明','22222','0000','地址D' 
go
create function f_getParent(@ID int,@atopname int)
returns varchar(40)
as
begin
    declare @ret varchar(40)

    while exists(select 1 from Agencies where PKId=@ID and ATopNode>@atopname)
    begin
        select @ID=b.PKId,@ret=','+rtrim(isnull(b.PKId,0))
        from
            Agencies a,Agencies b
        where
            a.PKId=@ID and b.PKId=a.ATopNode
    end

    set @ret=stuff(@ret,1,1,'')
	set @ret= isnull(@ret ,@id)
    return @ret
end
go


create proc proc_test
@ATopNode int
as
begin
declare @s varchar(8000)
set @s = ''
select @s = @s + ',['+IName+']= max(case when FType = '+ ltrim(PKId) + ' then FAccount else 0 end) '
from items
--print @s
set @s =  ('select 机构名称 = cast(a.AName as varchar)'+@s+'
,sum(FAccount) as  合计 from (select * from Agencies where ATopNode = '+ltrim(@ATopNode )+') a
left join Householders b on a.PKId = dbo.f_getParent(b.AgenciesId,'+ltrim(@ATopNode)+')
left join Financial    c on b.Pkid = c.HouseholdersId group by a.AName')

exec ( @s)
end 

go


--- TEST

declare @ATopNode int
set @ATopNode = 4

exec proc_test @ATopNode
/*
机构名称                           学杂费                                                   书本费                                                   服装费                                                   合计                                                    
------------------------------ ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 
川大计科系                          200.0                                                 0.0                                                   200.0                                                 400.0


*/

set @ATopNode = 5

exec proc_test @ATopNode
/*
机构名称                           学杂费                                                   书本费                                                   服装费                                                   合计                                                    
------------------------------ ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 
川大法学系                          0.0                                                   100.0                                                 0.0                                                   100.0


*/
set @ATopNode = 6

exec proc_test @ATopNode
/*
机构名称                           学杂费                                                   书本费                                                   服装费                                                   合计                                                    
------------------------------ ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 
华大计科系                          0.0                                                   300.0                                                 0.0                                                   300.0


*/

drop table Agencies,Items,Financial,Householders

drop function f_getparent
drop proc proc_test