用select left(id,2) ,sum(XXX) from a group by left(id,2) union select left(id,4) ,sum(XXX) from a group by left(id,4) union select left(id,6) ,sum(XXX) from a group by left(id,6) union select left(id,8) ,sum(XXX) from a group by left(id,8)
--按层更新父节点 --创建表 create table t (a varchar(20),b int) insert into t select '01', 0 union select '0101', 0 union select '010101', 0 union select '010102', 1 union select '01010201', 2 union select '01010202', 2 union select '0101020201', 5 union select '0102', 0 union select '010201', 4 union select '010202', 4 union select '02', 0 union select '0201', 3 union select '0202', 4 union select '020201', 4 union select '020202', 1 --select * from t order by a --创建存储过程 go create proc p_t as declare @l int select *,convert(varchar,'') as p ,0 as lvl into #t from t order by a desc update #t set lvl = len(a)/2 ,p=left(a,len(a)-2) select @l=max(lvl) from #t while @@rowcount>0 begin set @l=@l-1 update #t set b= e.f from (select p,sum(isnull(b,0)) as f from #t c where c.lvl=@l+1 group by c.p ) e where #t.lvl=@l and #t.a=e.p end select a,b from #t order by a go --执行 exec p_t go --删除 drop proc p_t go drop table t go
--按层更新父节点 --创建表 create table t (a varchar(20),b int) insert into t select '01', 0 union select '0101', 0 union select '010101', 0 union select '010102', 1 union select '01010201', 2 union select '01010202', 2 union select '0101020201', 5 union select '0102', 0 union select '010201', 4 union select '010202', 4 union select '02', 0 union select '0201', 3 union select '0202', 4 union select '020201', 4 union select '020202', 1 --select * from t order by a --创建存储过程 go create proc p_t as declare @l int select @l=max(len(a)/2) from t while @l>0 begin set @l=@l-1 update t set b= e.f from (select left(a,len(a)-2) as p,sum(isnull(b,0)) as f from t c where len(c.a)/2=@l+1 group by left(a,len(a)-2) ) e where len(t.a)/2=@l and t.a=e.p end select a,b from t order by a go --执行 exec p_t go --删除 drop proc p_t go drop table t go
union
select left(id,4) ,sum(XXX) from a group by left(id,4)
union
select left(id,6) ,sum(XXX) from a group by left(id,6)
union
select left(id,8) ,sum(XXX) from a group by left(id,8)
--创建表
create table t (a varchar(20),b int)
insert into t select '01', 0
union select '0101', 0
union select '010101', 0
union select '010102', 1
union select '01010201', 2
union select '01010202', 2
union select '0101020201', 5
union select '0102', 0
union select '010201', 4
union select '010202', 4
union select '02', 0
union select '0201', 3
union select '0202', 4
union select '020201', 4
union select '020202', 1
--select * from t order by a
--创建存储过程
go
create proc p_t
as
declare @l int
select *,convert(varchar,'') as p ,0 as lvl into #t from t order by a desc
update #t set lvl = len(a)/2 ,p=left(a,len(a)-2)
select @l=max(lvl) from #t
while @@rowcount>0
begin
set @l=@l-1
update #t set b= e.f
from (select p,sum(isnull(b,0)) as f from #t c where c.lvl=@l+1 group by c.p ) e
where #t.lvl=@l and #t.a=e.p
end
select a,b from #t order by a
go
--执行
exec p_t
go
--删除
drop proc p_t
go
drop table t
go
--创建表
create table t (a varchar(20),b int)
insert into t select '01', 0
union select '0101', 0
union select '010101', 0
union select '010102', 1
union select '01010201', 2
union select '01010202', 2
union select '0101020201', 5
union select '0102', 0
union select '010201', 4
union select '010202', 4
union select '02', 0
union select '0201', 3
union select '0202', 4
union select '020201', 4
union select '020202', 1
--select * from t order by a
--创建存储过程
go
create proc p_t
as
declare @l int
select @l=max(len(a)/2) from t
while @l>0
begin
set @l=@l-1
update t set b= e.f
from (select left(a,len(a)-2) as p,sum(isnull(b,0)) as f from t c where len(c.a)/2=@l+1 group by left(a,len(a)-2) ) e
where len(t.a)/2=@l and t.a=e.p
end
select a,b from t order by a
go
--执行
exec p_t
go
--删除
drop proc p_t
go
drop table t
go