导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

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

iky453 2008-01-08 10:39:40
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(你上次帮我解的)
...全文
15 点赞 收藏 1
写回复
1 条回复
切换为时间正序
请发表友善的回复…
发表回复
-狙击手- 2008-01-08
嘿嘿,看康熙王朝了,
加个判定

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

回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告