declare @参数 varchar(100) set @参数='Level2B'declare @tmp1 table (id int,ParentID int,name varchar(20)) insert @tmp1 select * from TB_Category where name=@参数 while @@rowcount>0 insert @tmp1 select a.* from TB_Category a,@tmp1 b where a.ParentID=b.ID and a.ID not in (select ID from @tmp1)select a.id,sum(b.categoryid) 和 from @tmp1 a,tb_fee b where a.id=b.Fee group by a.id
/*-- 得到指定id的子id列表 --*/ --不包含排序字段的情况 create function f_getchildid(@id int) returns @re table(id int) as begin insert into @re select id from tb where pid=@id while @@rowcount>0 insert into @re select a.id from tb a inner join @re b on a.pid=b.id where a.id not in(select id from @re) return end go--包含排序字段的情况 create function f_getchildidsort(@id int) returns @re table(id int,sortid varchar(8000)) as begin --为了数字排序正常,需要统一编码宽度 declare @idlen int,@idheader varchar(20) select @idlen=max(len(id)) ,@idheader=space(@idlen) from tb insert into @re select id,right(@idheader+cast(id as varchar),@idlen) from tb where pid=@id while @@rowcount>0 insert into @re select a.id,right(@idheader+cast(a.id as varchar),@idlen)+','+b.sortid from tb a inner join @re b on a.pid=b.id where a.id not in(select id from @re) return end go
set @参数='Level2B'declare @tmp1 table (id int,ParentID int,name varchar(20))
insert @tmp1 select * from TB_Category where name=@参数
while @@rowcount>0
insert @tmp1 select a.* from TB_Category a,@tmp1 b where a.ParentID=b.ID and a.ID not in (select ID from @tmp1)select a.id,sum(b.categoryid) 和 from @tmp1 a,tb_fee b where a.id=b.Fee group by a.id
树形数据的处理
http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=1.707095E-02
insert @TB_Category values(2, 1 , 'Level2A')
insert @TB_Category values(3, 1 , 'Level2B')
insert @TB_Category values(4, 2 , 'Level3A')
insert @TB_Category values(5, 3 , 'Level3B')
insert @TB_Category values(6,3 , 'Level3C')
declare @TB_Fee table (ID int,CategoryID int,Fee int)
insert @tb_fee values(1, 1 , 1)
insert @tb_fee values(2, 5 , 2)
insert @tb_fee values(3, 3 , 2)
insert @tb_fee values(4, 6 , 3)
insert @tb_fee values(5, 3 , 5)
insert @tb_fee values(6, 4 , 6)
insert @tb_fee values(7, 5 , 3)
insert @tb_fee values(8, 4 , 4)
insert @tb_fee values(9, 5 , 2)
insert @tb_fee values(10, 6 , 4)
insert @tb_fee values(11, 3 , 1)
insert @tb_fee values(12, 4 , 6)
insert @tb_fee values(13, 4 , 3)
insert @tb_fee values(14, 2 , 4)
insert @tb_fee values(15, 1 , 5)
insert @tb_fee values(16, 6 , 5)
insert @tb_fee values(17, 6 , 9)
declare @参数 varchar(100)set @参数='Level2B'declare @tmp1 table (id int,ParentID int,name varchar(20))
insert @tmp1 select * from @TB_Category where name=@参数
while @@rowcount>0
insert @tmp1 select a.* from @TB_Category a,@tmp1 b where a.ParentID=b.ID and a.ID not in (select ID from @tmp1)select a.id,sum(b.categoryid) 和 from @tmp1 a,@tb_fee b where a.id=b.Fee group by a.id/*id 和
----------- -----------
3 15
5 10
6 8(所影响的行数为 3 行)
*/
http://expert.csdn.net/Expert/topic/2465/2465539.xml?temp=.4092066
--不包含排序字段的情况
create function f_getchildid(@id int)
returns @re table(id int)
as
begin
insert into @re select id from tb where pid=@id
while @@rowcount>0
insert into @re select a.id
from tb a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
return
end
go--包含排序字段的情况
create function f_getchildidsort(@id int)
returns @re table(id int,sortid varchar(8000))
as
begin
--为了数字排序正常,需要统一编码宽度
declare @idlen int,@idheader varchar(20)
select @idlen=max(len(id))
,@idheader=space(@idlen)
from tb insert into @re select id,right(@idheader+cast(id as varchar),@idlen)
from tb where pid=@id
while @@rowcount>0
insert into @re select a.id,right(@idheader+cast(a.id as varchar),@idlen)+','+b.sortid
from tb a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
return
end
go
就是说Level1下面有Level2A,Level2B,Level2C
而Level2A下面有Level3A,Level3B
你的代码是求得Level1,Level2A,Level3A的三行求和吧?
我想要的是
Level3A,Level3B,Level2A,Level2B,Level2C,Level1
这样的结果
insert @TB_Category values(2, 1 , 'Level2A')
insert @TB_Category values(3, 1 , 'Level2B')
insert @TB_Category values(4, 2 , 'Level3A')
insert @TB_Category values(5, 3 , 'Level3B')
insert @TB_Category values(6,3 , 'Level3C')
declare @TB_Fee table (ID int,CategoryID int,Fee int)
insert @tb_fee values(1, 1 , 1)
insert @tb_fee values(2, 5 , 2)
insert @tb_fee values(3, 3 , 2)
insert @tb_fee values(4, 6 , 3)
insert @tb_fee values(5, 3 , 5)
insert @tb_fee values(6, 4 , 6)
insert @tb_fee values(7, 5 , 3)
insert @tb_fee values(8, 4 , 4)
insert @tb_fee values(9, 5 , 2)
insert @tb_fee values(10, 6 , 4)
insert @tb_fee values(11, 3 , 1)
insert @tb_fee values(12, 4 , 6)
insert @tb_fee values(13, 4 , 3)
insert @tb_fee values(14, 2 , 4)
insert @tb_fee values(15, 1 , 5)
insert @tb_fee values(16, 6 , 5)
insert @tb_fee values(17, 6 , 9)
declare @参数 varchar(100)set @参数='Level1'declare @tmp1 table (id int,ParentID int,name varchar(20))
insert @tmp1 select * from @TB_Category where name=@参数
while @@rowcount>0
insert @tmp1 select a.* from @TB_Category a,@tmp1 b where a.ParentID=b.ID and a.ID not in (select ID from @tmp1)select a.id,a.name,sum(b.categoryid) 和 from @tmp1 a,@tb_fee b where a.id=b.Fee group by a.id,a.name/*
id name 和
----------- -------------------- -----------
1 Level1 4
2 Level2A 13
3 Level2B 15
4 Level3A 12
5 Level3B 10
6 Level3C 8(所影响的行数为 6 行)
*/有错吗???????
insert @TB_Category values(2, 1 , 'Level2A')
insert @TB_Category values(3, 1 , 'Level2B')
insert @TB_Category values(4, 2 , 'Level3A')
insert @TB_Category values(5, 3 , 'Level3B')
insert @TB_Category values(6,3 , 'Level3C')
declare @TB_Fee table (ID int,CategoryID int,Fee int)
insert @tb_fee values(1, 1 , 1)
insert @tb_fee values(2, 5 , 2)
insert @tb_fee values(3, 3 , 2)
insert @tb_fee values(4, 6 , 3)
insert @tb_fee values(5, 3 , 5)
insert @tb_fee values(6, 4 , 6)
insert @tb_fee values(7, 5 , 3)
insert @tb_fee values(8, 4 , 4)
insert @tb_fee values(9, 5 , 2)
insert @tb_fee values(10, 6 , 4)
insert @tb_fee values(11, 3 , 1)
insert @tb_fee values(12, 4 , 6)
insert @tb_fee values(13, 4 , 3)
insert @tb_fee values(14, 2 , 4)
insert @tb_fee values(15, 1 , 5)
insert @tb_fee values(16, 6 , 5)
insert @tb_fee values(17, 6 , 9)
declare @参数 varchar(100)set @参数='Level2B'declare @tmp1 table (id int,ParentID int,name varchar(20),层次 int)
declare @i int
select @i=1insert @tmp1 select *,@i from @TB_Category where name=@参数
while @@rowcount>0
begin
set @i=@i+1
insert @tmp1 select a.*,@i from @TB_Category a,@tmp1 b where a.ParentID=b.ID and a.ID not in (select ID from @tmp1)
endselect *,(select sum(CategoryID) from @tb_fee where id=a.id or id in (select id from @tmp1 where 层次>a.层次)) [sum] from @tmp1 a
/*
id ParentID name 层次 sum
----------- ----------- -------------------- ----------- -----------
3 1 Level2B 1 10
5 3 Level3B 2 3
6 3 Level3C 2 4(所影响的行数为 3 行)*/
insert @TB_Category values(2, 1 , 'Level2A')
insert @TB_Category values(3, 1 , 'Level2B')
insert @TB_Category values(4, 2 , 'Level3A')
insert @TB_Category values(5, 3 , 'Level3B')
insert @TB_Category values(6,3 , 'Level3C')
declare @TB_Fee table (ID int,CategoryID int,Fee int)
insert @tb_fee values(1, 1 , 1)
insert @tb_fee values(2, 5 , 2)
insert @tb_fee values(3, 3 , 2)
insert @tb_fee values(4, 6 , 3)
insert @tb_fee values(5, 3 , 5)
insert @tb_fee values(6, 4 , 6)
insert @tb_fee values(7, 5 , 3)
insert @tb_fee values(8, 4 , 4)
insert @tb_fee values(9, 5 , 2)
insert @tb_fee values(10, 6 , 4)
insert @tb_fee values(11, 3 , 1)
insert @tb_fee values(12, 4 , 6)
insert @tb_fee values(13, 4 , 3)
insert @tb_fee values(14, 2 , 4)
insert @tb_fee values(15, 1 , 5)
insert @tb_fee values(16, 6 , 5)
insert @tb_fee values(17, 6 , 9)
declare @参数 varchar(100)set @参数='Level1'declare @tmp1 table (id int,ParentID int,name varchar(20),层次 int)
declare @i int
select @i=1insert @tmp1 select *,@i from @TB_Category where name=@参数
while @@rowcount>0
begin
set @i=@i+1
insert @tmp1 select a.*,@i from @TB_Category a,@tmp1 b where a.ParentID=b.ID and a.ID not in (select ID from @tmp1)
endselect *,(select sum(CategoryID) from @tb_fee where id=a.id or id in (select id from @tmp1 where 层次>a.层次)) [sum] from @tmp1 a
/*
id ParentID name 层次 sum
----------- ----------- -------------------- ----------- -----------
1 0 Level1 1 22
2 1 Level2A 2 18
3 1 Level2B 2 16
4 2 Level3A 3 6
5 3 Level3B 3 3
6 3 Level3C 3 4(所影响的行数为 6 行)*/
insert TB_Category values(2, 1 , 'Level2A')
insert TB_Category values(3, 1 , 'Level2B')
insert TB_Category values(4, 2 , 'Level3A')
insert TB_Category values(5, 3 , 'Level3B')
insert TB_Category values(6,3 , 'Level3C')
create table TB_Fee (ID int,CategoryID int,Fee int)
insert tb_fee values(1, 1 , 1)
insert tb_fee values(2, 5 , 2)
insert tb_fee values(3, 3 , 2)
insert tb_fee values(4, 6 , 3)
insert tb_fee values(5, 3 , 5)
insert tb_fee values(6, 4 , 6)
insert tb_fee values(7, 5 , 3)
insert tb_fee values(8, 4 , 4)
insert tb_fee values(9, 5 , 2)
insert tb_fee values(10, 6 , 4)
insert tb_fee values(11, 3 , 1)
insert tb_fee values(12, 4 , 6)
insert tb_fee values(13, 4 , 3)
insert tb_fee values(14, 2 , 4)
insert tb_fee values(15, 1 , 5)
insert tb_fee values(16, 6 , 5)
insert tb_fee values(17, 6 , 9)drop function getnum
create function getnum(@id int)
returns int
as
begin
declare @a int
select @a=sum(CategoryID) from tb_fee where id=@id
if exists(select 1 from TB_Category where ParentID=@id)
select @a=@a+dbo.getnum(id) from TB_Category where ParentID=@id
return @a
end
go
--下面开始求
declare @参数 varchar(100)set @参数='Level1'declare @tmp1 table (id int,ParentID int,name varchar(20))
insert @tmp1 select * from TB_Category where name=@参数
while @@rowcount>0
insert @tmp1 select a.* from TB_Category a,@tmp1 b where a.ParentID=b.ID and a.ID not in (select ID from @tmp1)select *,dbo.getnum(id) 和 from @tmp1 a