--示例--示例数据
create table tcustomer(customerid int,customerno char(4),customername varchar(10))
insert tcustomer select 1,'1001','A'
union all select 2,'1002','B'
union all select 3,'1003','C'
union all select 4,'1004','D'
union all select 5,'1005','E'
union all select 6,'1006','F'
union all select 7,'1007','G'
union all select 8,'1008','H'create table trelate(relateid int,customerid int,parentid int,levelid int)
insert trelate select 1,1,0,0
union all select 2,2,1,1
union all select 3,3,2,2
union all select 4,4,3,3
union all select 5,5,4,4
union all select 6,6,5,5
union all select 7,7,6,6
union all select 8,8,7,7create table tsale(saleid int,customerid int,amount int)
insert tsale select 1,1,100
union all select 2,2,100
union all select 3,3,100
union all select 4,4,100
union all select 5,5,100
union all select 6,6,100
union all select 7,7,100
union all select 8,8,100
go--计算处理的存储过程
create proc p_calc
@cutomerid int, --人员id
@level int=-1 --计算级别,-1是计算到最大级别,0是本人(级别是针对本人而言,与levelid无直接关系
as
set nocount on
--计算级别
declare @l int
declare @t table(customerid int,level int)
set @l=0
insert @t select @cutomerid,@l
while @@rowcount>0 and (@level=-1 or @l<@level)
begin
set @l=@l+1
insert @t select a.customerid,@l
from trelate a,@t b
where a.parentid=b.customerid
and b.level=@l-1
end--计算结果
select customerno=case
when b.level=0 then a.customerno
when b.level is null then '累计'
else '' end,
customername=case when b.level=0 then a.customername else '' end,
level=isnull(cast(b.level as varchar),''),
amount=sum(c.amount)
from tcustomer a,@t b,tsale c
where a.customerid=@cutomerid
and b.customerid=c.customerid
group by a.customerno,a.customername,
b.level with rollup
having grouping(b.level)=0
or grouping(a.customerno)=1
go--调用
exec p_calc 1,4
go--删除测试
drop table tcustomer,trelate,tsale
drop proc p_calc/*--结果customerno customername level amount
---------- ------------ -------- -------------
1001 A 0 100
1 100
2 100
3 100
4 100
累计 500
--*/
create table tcustomer(customerid int,customerno char(4),customername varchar(10))
insert tcustomer select 1,'1001','A'
union all select 2,'1002','B'
union all select 3,'1003','C'
union all select 4,'1004','D'
union all select 5,'1005','E'
union all select 6,'1006','F'
union all select 7,'1007','G'
union all select 8,'1008','H'create table trelate(relateid int,customerid int,parentid int,levelid int)
insert trelate select 1,1,0,0
union all select 2,2,1,1
union all select 3,3,2,2
union all select 4,4,3,3
union all select 5,5,4,4
union all select 6,6,5,5
union all select 7,7,6,6
union all select 8,8,7,7create table tsale(saleid int,customerid int,amount int)
insert tsale select 1,1,100
union all select 2,2,100
union all select 3,3,100
union all select 4,4,100
union all select 5,5,100
union all select 6,6,100
union all select 7,7,100
union all select 8,8,100
go--计算处理的存储过程
create proc p_calc
@cutomerid int, --人员id
@level int=-1 --计算级别,-1是计算到最大级别,0是本人(级别是针对本人而言,与levelid无直接关系
as
set nocount on
--计算级别
declare @l int
declare @t table(customerid int,level int)
set @l=0
insert @t select @cutomerid,@l
while @@rowcount>0 and (@level=-1 or @l<@level)
begin
set @l=@l+1
insert @t select a.customerid,@l
from trelate a,@t b
where a.parentid=b.customerid
and b.level=@l-1
end--计算结果
select customerno=case
when b.level=0 then a.customerno
when b.level is null then '累计'
else '' end,
customername=case when b.level=0 then a.customername else '' end,
level=isnull(cast(b.level as varchar),''),
amount=sum(c.amount)
from tcustomer a,@t b,tsale c
where a.customerid=@cutomerid
and b.customerid=c.customerid
group by a.customerno,a.customername,
b.level with rollup
having grouping(b.level)=0
or grouping(a.customerno)=1
go--调用
exec p_calc 1,4
go--删除测试
drop table tcustomer,trelate,tsale
drop proc p_calc/*--结果customerno customername level amount
---------- ------------ -------- -------------
1001 A 0 100
1 100
2 100
3 100
4 100
累计 500
--*/
create proc p_calc
@cutomerid int, --人员id
@level int=-1 --计算级别,-1是计算到最大级别,0是本人(级别是针对本人而言,与levelid无直接关系
as
set nocount on
--计算级别
declare @l int
declare @t table(customerid int,level int)
set @l=0
insert @t select @cutomerid,@l
while @@rowcount>0 and (@level=-1 or @l<@level)
begin
set @l=@l+1
insert @t select a.customerid,@l
from trelate a,@t b
where a.parentid=b.customerid
and b.level=@l-1
end--计算结果
select a.customerno,a.customername,amount=sum(c.amount)
from tcustomer a,@t b,tsale c
where a.customerid=@cutomerid
and b.customerid=c.customerid
group by a.customerno,a.customername
go
create table tcustomer
(
cutomerid char(1),
customerno char(4),
customername char(1)
)insert into tcustomer
select '1', '1001', 'A'
union
select '2', '1002', 'B'
union
select '3', '1003', 'C'
union
select '4', '1004', 'D'
union
select '5', '1005', 'E'
union
select '6', '1006', 'F'
union
select '7', '1007', 'G'
union
select '8', '1008', 'H'
select *
into trelate
from
(
select '1' as relateid, '1' as customerid,'0' as parentid,0 levelid
union
select '2', '2', '1', 1
union
select '3', '3', '2', 2
union
select '4', '4', '3', 3
union
select '5', '5', '3', 3
union
select '6', '6', '5', 5
union
select '7', '7', '6', 6
union
select '8', '8', '7', 7
) aselect *
into tsales
from
(
select '1' saleid ,'1' customerid ,100 amount
union
select '2', '2', 100
union
select '3', '3', 100
union
select '4', '4', 100
union
select '5', '5', 100
union
select '6', '6', 100
union
select '7', '7', 100
union
select '8', '8', 100
) t--存储过程
create procedure p_expand @customerid char(1),@level int
as
begin
declare @s_curid char(1)
declare @i_level int
declare @i_acount intset nocount on
select customerid,parentid,0 level into #expand from trelate where customerid = @customeridset @i_acount = 0
declare c_expand cursor DYNAMIC for
select customerid,level from #expand
open c_expand
fetch next from c_expand into @s_curid,@i_level
while @@fetch_status = 0
begin
insert #expand
select customerid,@s_curid,@i_level + 1
from trelate where parentid = @s_curid
if @i_level = @level
begin
select @i_acount = @i_acount + IsNull(sum(amount),0)
from tsales
where customerid = @s_curid
end
delete #expand where customerid = @s_curid
fetch first from c_expand into @s_curid,@i_level
end
close c_expand
deallocate c_expand
select @i_acount
end--调用
exec p_expand '1',3