写一个函数,生成各个ID所对应的节点路径以逗号分隔,存入临时表#t(也可以用子查询来代替) 比如 ID path num 1 1 10 2 1,2 10 3 1,2,3 10 4 1,2,3,4 10 5 1,2,3,5 10统计时 select id,(select sum(b.num) from #t b where charindex(a.id,b.path)=1) from tb a 或者 select a.id,sum(b.num) from tb a inner join #t b on charindex(a.id,b.path)=1 group by a.id那个函数我就不写了.关于得到无级分类某个节点路径的搜搜就有很多.
写错了.不是a.idselect id,(select sum(b.num) from #t b where charindex(a.path,b.path)=1) from #t a 或者 select a.id,sum(b.num) from #t a inner join #t b on charindex(a.path,b.path)=1 group by a.id
比如
ID path num
1 1 10
2 1,2 10
3 1,2,3 10
4 1,2,3,4 10
5 1,2,3,5 10统计时
select id,(select sum(b.num) from #t b where charindex(a.id,b.path)=1) from tb a
或者
select a.id,sum(b.num)
from tb a
inner join #t b
on charindex(a.id,b.path)=1
group by a.id那个函数我就不写了.关于得到无级分类某个节点路径的搜搜就有很多.
或者
select a.id,sum(b.num)
from #t a
inner join #t b
on charindex(a.path,b.path)=1
group by a.id