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(你上次帮我解的)
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(你上次帮我解的)
解决方案 »
- 2003 R2升级到2003 SP2的风险?难!!!
- 求累计发生数和总计数
- 救命呀!!添加列名的问题
- MySQL 数据库被删掉 “customers_basket",
- 我装的是SQL2000个人版
- 100分求sql server 2000 个人版下载地址!(一定要能下载!)
- 做一个题库系统,请问:试题如何存入?以什么方式?存为什么?定义为什么类型?存好后是哪个目录的哪个文件?如果试题里有图怎么存?
- 谁能告诉我,SQLServer 7.0数据库DateTime字段的比较方法?
- 请KittyWang(Kitty)和stingzy(小道)进来拿分。
- 我直接导出的,但是运行以来就不对,为什么???
- insert语句通不过,不知那里错了
- 记录成对进行比较
加个判定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