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 @ATopNodehttp://topic.csdn.net/u/20080105/04/f0b88745-4e43-41b2-a84e-344bcbd02ddf.html(你上次帮我解的)

解决方案 »

  1.   

    嘿嘿,看康熙王朝了,
    加个判定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
    --- TESTdeclare @ATopNode int
    set @ATopNode = 4exec proc_test @ATopNode
    /*
    机构名称                           学杂费                                                   书本费                                                   服装费                                                   合计                                                    
    ------------------------------ ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 
    川大计科系                          200.0                                                 0.0                                                   200.0                                                 400.0
    */set @ATopNode = 5exec proc_test @ATopNode
    /*
    机构名称                           学杂费                                                   书本费                                                   服装费                                                   合计                                                    
    ------------------------------ ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 
    川大法学系                          0.0                                                   100.0                                                 0.0                                                   100.0
    */
    set @ATopNode = 6exec proc_test @ATopNode
    /*
    机构名称                           学杂费                                                   书本费                                                   服装费                                                   合计                                                    
    ------------------------------ ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 
    华大计科系                          0.0                                                   300.0                                                 0.0                                                   300.0
    */drop table Agencies,Items,Financial,Householdersdrop function f_getparent
    drop proc proc_test