22,207
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: [tb]
if object_id('folder') is not null drop table folder
create table folder (pkid int,parent_id int,name varchar(6),filecount int)
insert into folder
select 2,-1,'根目录',1 union all
select 3,2,'音乐',2 union all
select 4,2,'照片',3 union all
select 5,3,'轻音乐',4 union all
select 6,3,'摇滚乐',5
go
create proc sp_wsp
as
declare @t table(pkid int,parent_id int,name varchar(6),totalcount int,lev int)
declare @lev int
set @lev=0
insert into @t select pkid,parent_id,name,filecount,@lev from folder a where not exists(select 1 from folder where parent_Id=a.pkid)
while(@@rowcount>0)
begin
set @lev=@Lev+1
insert into @t select distinct a.pkid,a.parent_id,a.name,totalcount=(select sum(totalcount) from @t where parent_id=a.pkid)+a.filecount,@lev from folder a,@t b
where b.parent_id=a.pkid and @lev=lev+1
end
select pkid,parent_id,name,totalcount from @t a
where not exists(select 1 from @t where pkid=a.pkid and lev>a.lev) order by pkid
go
--执行
exec sp_wsp
--结果:
pkid parent_id name totalcount
----------- ----------- ------ -----------
2 -1 根目录 15
3 2 音乐 11
4 2 照片 3
5 3 轻音乐 4
6 3 摇滚乐 5