TO:大力 用你的测试语句可以得到最终的效果,但下面语句中如果不能根据#dirsort表name列的相似性 来进行汇总时又该如何编写?? select *,(select count(*) from #product where parentid in (select id from #dirsort where name like tem.name+'%')) 记录数 from #dirsort tem
--利用下面的自定义函数就可以了.--创建自定义函数 --自定义函数--获取编码累计 create function f_getmergid(@id int) returns varchar(8000) as begin declare @re varchar(8000),@pid int --为了数字排序正常,需要统一编码宽度 declare @idheader varchar(20) --得到编码累计 set @re=cast(@id as varchar) select @pid=parent from dirsort where id=@id while @@rowcount>0 select @re=cast(@pid as varchar)+'.'+@re ,@pid=parent from dirsort where id=@pid return(@re) end go
--测试:create table dirsort(id int,parent int,name varchar(10)) insert dirsort values(1 ,0, 'dir1') ---根目录1 insert dirsort values(2 , 0 , 'dir2') ---根目录2 insert dirsort values(3 , 1 , 'dir1.1') ---子目录1.1 insert dirsort values(4 , 1 , 'dir1.2') ---子目录1.2 insert dirsort values(5 , 2 , 'dir2.1') ---子目录2.1 insert dirsort values(6 , 2 , 'dir2.2') ---子目录2.2 insert dirsort values(7, 3 ,'dir1.1.1') ---子目录1.1.1create table product(pid int,parentId int,pname varchar(10)) insert product values(1 , 3 , 'p1') --子目录1.1下的产品 insert product values(2 , 3 , 'p2') --子目录1.1下的产品 insert product values(3 , 4, 'p3') --子目录1.2下的产品 insert product values(4 , 4 , 'p4') --子目录1.2下的产品 insert product values(5 , 5 , 'p5') --子目录2.1下的产品 insert product values(6 , 6 , 'p6') --子目录2.2下的产品 insert product values(7, 7 , 'p7') --子目录1.1.1下的产品go --创建自定义函数 --自定义函数--获取编码累计 create function f_getmergid(@id int) returns varchar(8000) as begin declare @re varchar(8000),@pid int --为了数字排序正常,需要统一编码宽度 declare @idheader varchar(20) --得到编码累计 set @re=cast(@id as varchar) select @pid=parent from dirsort where id=@id while @@rowcount>0 select @re=cast(@pid as varchar)+'.'+@re ,@pid=parent from dirsort where id=@pid return(@re) end go--统计 select *, 记录数=(select count(*) from product where dbo.f_getmergid(parentid) like dbo.f_getmergid(a.id)+'%') from dirsort a go --删除环境 drop table dirsort,product drop function f_getmergid
--如果从效率出发,可以创建这样一个函数: create function f_getmergidall() returns @tb table(id int,parent int,idmerg varchar(8000),level int) as begin declare @level int set @level=0 insert into @tb select id,parent,cast(id as varchar),@level from dirsort where parent not in(select id from dirsort) while @@rowcount>0 begin set @level=@level+1 insert into @tb select a.id,a.parent,b.idmerg+'.'+cast(a.id as varchar),@level from dirsort a inner join @tb b on a.parent=b.id where b.level=@level-1 end return end go
--然后用这种方法统计 select id,parent,name ,记录数=(select count(*) from product a inner join dbo.f_getmergidall() b on a.parentid=b.id where b.idmerg like aa.idmerg) from ( select a.*,idmerg=b.idmerg+'%' from dirsort a inner join dbo.f_getmergidall() b on a.id=b.id ) aa
就可以了
insert #dirsort values(1 ,0, 'dir1') ---根目录1
insert #dirsort values(2 , 0 , 'dir2') ---根目录2
insert #dirsort values(3 , 1 , 'dir1.1') ---子目录1.1
insert #dirsort values(4 , 1 , 'dir1.2') ---子目录1.2
insert #dirsort values(5 , 2 , 'dir2.1') ---子目录2.1
insert #dirsort values(6 , 2 , 'dir2.2') ---子目录2.2
insert #dirsort values(7, 3 ,'dir1.1.1') ---子目录1.1.1create table #product(pid int,parentId int,pname varchar(10))
insert #product values(1 , 3 , 'p1') --子目录1.1下的产品
insert #product values(2 , 3 , 'p2') --子目录1.1下的产品
insert #product values(3 , 4, 'p3') --子目录1.2下的产品
insert #product values(4 , 4 , 'p4') --子目录1.2下的产品
insert #product values(5 , 5 , 'p5') --子目录2.1下的产品
insert #product values(6 , 6 , 'p6') --子目录2.2下的产品
insert #product values(7, 7 , 'p7') --子目录1.1.1下的产品select *,(select count(*) from #product where parentid in (select id from #dirsort where name like tem.name+'%')) 记录数 from #dirsort tem
go
drop table #dirsort,#product
用你的方法可以统计出每一层目录的记录数,但无法统计出各层根目录下的总数!
表dirsort
id parent name 记录数(用上述方法) 记录数(正确数)
1 0 dir1 0 ---根目录1 5
2 0 dir2 0 ---根目录2 2
3 1 dir1.1 2 ---子目录1.1 3
4 1 dir1.2 2 ---子目录1.2 2
5 2 dir2.1 1 ---子目录2.1 1
6 2 dir2.2 1 ---子目录2.2 1
7 3 dir1.1.1 1 ---子目录1.1.1 1
http://expert.csdn.net/Expert/topic/1375/1375432.xml?temp=.8570978
用你的测试语句可以得到最终的效果,但下面语句中如果不能根据#dirsort表name列的相似性
来进行汇总时又该如何编写??
select *,(select count(*) from #product where parentid in (select id from #dirsort where name like tem.name+'%')) 记录数 from #dirsort tem
--自定义函数--获取编码累计
create function f_getmergid(@id int)
returns varchar(8000)
as
begin
declare @re varchar(8000),@pid int --为了数字排序正常,需要统一编码宽度
declare @idheader varchar(20) --得到编码累计
set @re=cast(@id as varchar)
select @pid=parent from dirsort where id=@id
while @@rowcount>0
select @re=cast(@pid as varchar)+'.'+@re
,@pid=parent from dirsort where id=@pid
return(@re)
end
go
insert dirsort values(1 ,0, 'dir1') ---根目录1
insert dirsort values(2 , 0 , 'dir2') ---根目录2
insert dirsort values(3 , 1 , 'dir1.1') ---子目录1.1
insert dirsort values(4 , 1 , 'dir1.2') ---子目录1.2
insert dirsort values(5 , 2 , 'dir2.1') ---子目录2.1
insert dirsort values(6 , 2 , 'dir2.2') ---子目录2.2
insert dirsort values(7, 3 ,'dir1.1.1') ---子目录1.1.1create table product(pid int,parentId int,pname varchar(10))
insert product values(1 , 3 , 'p1') --子目录1.1下的产品
insert product values(2 , 3 , 'p2') --子目录1.1下的产品
insert product values(3 , 4, 'p3') --子目录1.2下的产品
insert product values(4 , 4 , 'p4') --子目录1.2下的产品
insert product values(5 , 5 , 'p5') --子目录2.1下的产品
insert product values(6 , 6 , 'p6') --子目录2.2下的产品
insert product values(7, 7 , 'p7') --子目录1.1.1下的产品go
--创建自定义函数
--自定义函数--获取编码累计
create function f_getmergid(@id int)
returns varchar(8000)
as
begin
declare @re varchar(8000),@pid int --为了数字排序正常,需要统一编码宽度
declare @idheader varchar(20) --得到编码累计
set @re=cast(@id as varchar)
select @pid=parent from dirsort where id=@id
while @@rowcount>0
select @re=cast(@pid as varchar)+'.'+@re
,@pid=parent from dirsort where id=@pid
return(@re)
end
go--统计
select *,
记录数=(select count(*) from product
where dbo.f_getmergid(parentid) like dbo.f_getmergid(a.id)+'%')
from dirsort a
go
--删除环境
drop table dirsort,product
drop function f_getmergid
http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=.3965876
create function f_getmergidall()
returns @tb table(id int,parent int,idmerg varchar(8000),level int)
as
begin
declare @level int
set @level=0
insert into @tb select id,parent,cast(id as varchar),@level from dirsort
where parent not in(select id from dirsort)
while @@rowcount>0
begin
set @level=@level+1
insert into @tb select a.id,a.parent,b.idmerg+'.'+cast(a.id as varchar),@level
from dirsort a inner join @tb b on a.parent=b.id where b.level=@level-1
end
return
end
go
select id,parent,name
,记录数=(select count(*) from product a inner join dbo.f_getmergidall() b on a.parentid=b.id
where b.idmerg like aa.idmerg)
from (
select a.*,idmerg=b.idmerg+'%'
from dirsort a inner join dbo.f_getmergidall() b on a.id=b.id
) aa