folder 表结构为: pkid int,/*文件夹主键*/
parent_id int,/*父文件夹*/
name varchar,/*文件夹名称*/
filecount int/*本目录所包含的文件数量(不包含子目录的!)*/ insert into folder (pkid,parent_id,name,filecount) values(1,-1,"根目录",1);
insert into folder (pkid,parent_id,name,filecount) values(2,1,"音乐",2);
insert into folder (pkid,parent_id,name,filecount) values(3,2,"摇滚乐",3);
insert into folder (pkid,parent_id,name,filecount) values(4,1,"电影",1);
insert into folder (pkid,parent_id,name,filecount) values(5,4,"国产电影",2);
insert into folder (pkid,parent_id,name,filecount) values(6,5,"喜剧",3); 如何才能得到下列结果
pkid parent_id name totalcount
1 -1 根目录 12
2 1 音乐 5
3 2 摇滚乐 3
4 1 电影 6
5 4 国产电影 5
6 5 喜剧 3 上午王sir回复
create table folder(pkid int,parent_id int,name varchar(20),filecount int)
insert into folder (pkid,parent_id,name,filecount) values(1,-1,'根目录',1);
insert into folder (pkid,parent_id,name,filecount) values(2,1,'音乐',2);
insert into folder (pkid,parent_id,name,filecount) values(3,2,'摇滚乐',3);
insert into folder (pkid,parent_id,name,filecount) values(4,1,'电影',1);
insert into folder (pkid,parent_id,name,filecount) values(5,4,'国产电影',2);
insert into folder (pkid,parent_id,name,filecount) values(6,5,'喜剧',3);
go
create proc s_wsp
as
;with wsp
as
(
select *,totalcount=filecount from folder a where not exists(select 1 from folder where parent_Id=a.pkid)
union all
select a.*,totalcount=b.totalcount+a.filecount from folder a,wsp b where a.pkid=b.parent_Id and
a.parent_Id!=-1
)
select pkid,parent_id,name,totalcount From wsp
union all
select pkid,parent_id,name,(select sum(filecount) from wsp)+filecount from folder where parent_Id=-1
order by pkid
goexec s_wsp--结果:
pkid parent_id name totalcount
----------- ----------- -------------------- -----------
1 -1 根目录 12
2 1 音乐 5
3 2 摇滚乐 3
4 1 电影 6
5 4 国产电影 5
6 5 喜剧 3但是如果像下面这样某目录存在不止一个子目录,就不正确了pkid parent_id name filecount
2 -1 根目录 1
3 2 音乐 2
4 2 照片 3
5 3 轻音乐 4
6 3 摇滚乐 5得出这个结果
2 -1 根目录 17
3 2 音乐 7
3 2 音乐 6
4 2 照片 3
5 3 轻音乐 4
6 3 摇滚乐 5继续求解,希望王sir及其它朋友帮助。
parent_id int,/*父文件夹*/
name varchar,/*文件夹名称*/
filecount int/*本目录所包含的文件数量(不包含子目录的!)*/ insert into folder (pkid,parent_id,name,filecount) values(1,-1,"根目录",1);
insert into folder (pkid,parent_id,name,filecount) values(2,1,"音乐",2);
insert into folder (pkid,parent_id,name,filecount) values(3,2,"摇滚乐",3);
insert into folder (pkid,parent_id,name,filecount) values(4,1,"电影",1);
insert into folder (pkid,parent_id,name,filecount) values(5,4,"国产电影",2);
insert into folder (pkid,parent_id,name,filecount) values(6,5,"喜剧",3); 如何才能得到下列结果
pkid parent_id name totalcount
1 -1 根目录 12
2 1 音乐 5
3 2 摇滚乐 3
4 1 电影 6
5 4 国产电影 5
6 5 喜剧 3 上午王sir回复
create table folder(pkid int,parent_id int,name varchar(20),filecount int)
insert into folder (pkid,parent_id,name,filecount) values(1,-1,'根目录',1);
insert into folder (pkid,parent_id,name,filecount) values(2,1,'音乐',2);
insert into folder (pkid,parent_id,name,filecount) values(3,2,'摇滚乐',3);
insert into folder (pkid,parent_id,name,filecount) values(4,1,'电影',1);
insert into folder (pkid,parent_id,name,filecount) values(5,4,'国产电影',2);
insert into folder (pkid,parent_id,name,filecount) values(6,5,'喜剧',3);
go
create proc s_wsp
as
;with wsp
as
(
select *,totalcount=filecount from folder a where not exists(select 1 from folder where parent_Id=a.pkid)
union all
select a.*,totalcount=b.totalcount+a.filecount from folder a,wsp b where a.pkid=b.parent_Id and
a.parent_Id!=-1
)
select pkid,parent_id,name,totalcount From wsp
union all
select pkid,parent_id,name,(select sum(filecount) from wsp)+filecount from folder where parent_Id=-1
order by pkid
goexec s_wsp--结果:
pkid parent_id name totalcount
----------- ----------- -------------------- -----------
1 -1 根目录 12
2 1 音乐 5
3 2 摇滚乐 3
4 1 电影 6
5 4 国产电影 5
6 5 喜剧 3但是如果像下面这样某目录存在不止一个子目录,就不正确了pkid parent_id name filecount
2 -1 根目录 1
3 2 音乐 2
4 2 照片 3
5 3 轻音乐 4
6 3 摇滚乐 5得出这个结果
2 -1 根目录 17
3 2 音乐 7
3 2 音乐 6
4 2 照片 3
5 3 轻音乐 4
6 3 摇滚乐 5继续求解,希望王sir及其它朋友帮助。
http://topic.csdn.net/u/20100204/10/52a8a9fd-1c5a-48f6-92d8-c42b0e65f594.html
16楼
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
gocreate 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