--还是一样:--创建处理函数
create function f_id()
returns @re table(id int,sid varchar(8000),level int)
as
begin
declare @l int --为了数字排序正常,需要统一编码宽度
declare @idlen int,@idheader varchar(20)
select @idlen=max(len(id)),@idheader=space(@idlen)
from base --得到编码累计
set @l=0
insert @re select id,right(@idheader+cast(id as varchar),@idlen)+',',@l
from base where parent_id=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,b.sid+right(@idheader+cast(a.id as varchar),@idlen)+',',@l
from base a join @re b on a.parent_id=b.id
where b.level=@l-1
end
return
end
go--调用函数实现楼主的统计
select title=space(b.level*2)+a.title,次数=count(d.id)
from base a
join f_id() b on a.id=b.id
left join f_id() c on c.sid like b.sid+'%'
left join info d on c.id=d.id
group by a.id,a.title,b.sid,b.level
order by b.sid
go
create function f_id()
returns @re table(id int,sid varchar(8000),level int)
as
begin
declare @l int --为了数字排序正常,需要统一编码宽度
declare @idlen int,@idheader varchar(20)
select @idlen=max(len(id)),@idheader=space(@idlen)
from base --得到编码累计
set @l=0
insert @re select id,right(@idheader+cast(id as varchar),@idlen)+',',@l
from base where parent_id=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,b.sid+right(@idheader+cast(a.id as varchar),@idlen)+',',@l
from base a join @re b on a.parent_id=b.id
where b.level=@l-1
end
return
end
go--调用函数实现楼主的统计
select title=space(b.level*2)+a.title,次数=count(d.id)
from base a
join f_id() b on a.id=b.id
left join f_id() c on c.sid like b.sid+'%'
left join info d on c.id=d.id
group by a.id,a.title,b.sid,b.level
order by b.sid
go
create table base(id int,parent_id int,title varchar(10))
insert base select 1, 0,'A'
union all select 2, 0,'B'
union all select 3, 0,'C'
union all select 4, 1,'D'
union all select 5, 1,'E'
union all select 6, 2,'F'
union all select 7, 2,'G'
union all select 8, 2,'H'
union all select 9, 3,'I'
union all select 10,3,'J'
union all select 11,4,'K'
union all select 12,4,'L'create table info(id int,content varchar(10))
insert info select 11,'xxx'
union all select 12,'xxx'
union all select 12,'xxx'
union all select 9, 'xxx'
union all select 9, 'xxx'
union all select 10,'xxx'
union all select 10,'xxx'
union all select 7, 'xxx'
union all select 8, 'xxx'
union all select 5, 'xxx'
union all select 5, 'xxx'
union all select 2, 'xxx'
union all select 2, 'xxx'
go--创建处理函数
create function f_id()
returns @re table(id int,sid varchar(8000),level int)
as
begin
declare @l int --为了数字排序正常,需要统一编码宽度
declare @idlen int,@idheader varchar(20)
select @idlen=max(len(id)),@idheader=space(@idlen)
from base --得到编码累计
set @l=0
insert @re select id,right(@idheader+cast(id as varchar),@idlen)+',',@l
from base where parent_id=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,b.sid+right(@idheader+cast(a.id as varchar),@idlen)+',',@l
from base a join @re b on a.parent_id=b.id
where b.level=@l-1
end
return
end
go--调用函数实现楼主的统计
select title=space(b.level*2)+a.title,次数=count(d.id)
from base a
join f_id() b on a.id=b.id
left join f_id() c on c.sid like b.sid+'%'
left join info d on c.id=d.id
group by a.id,a.title,b.sid,b.level
order by b.sid
go--删除测试
drop table base,info
drop function f_id/*--测试结果title 次数
---------- -----------
A 5
D 3
K 1
L 2
E 2
B 4
F 0
G 1
H 1
C 4
I 2
J 2(所影响的行数为 12 行)
--*/