层级架构的汇总累加

数据库结构：

declare @team table--组定义
(
 team_id int primary key,
 team_name nchar(10),
 parent_team_id int,
 team_rank char(1) --A,B,C,D 级别从高到低，高级别组可以含有低级别组的下级
)

declare @member table--营销员定义
(
 member_id int primary key,
 team_id int,
 member_name nchar(10)
)

declare @sale table--销售定义
(
 waterid int identity(1,1) primary key,
 member_id int,

 team_id int,--考虑业务员可能会换组，这里增设一个team_id字段
 sale_date datetime,--date only
 _money float
)

 

说明：

1.A级组可以直接含有C或D的下级组（即不一定是完整的ABCD架构）

2.营销员可以属于任意级别的组

3.此数据结构为简单结构，实际使用中可以使用冗余进行查询优化，因此处没有类似应用，暂时不考虑这个问题

 

 

 

问题说明：

为了促进公司各营销机构的衣物推动，销售部门对公司现有的销售架构临时重新切割成三类

type1  A+D ：即所有的A级组+A级组直属（即parent_team_id是A级组Id的）D级组 和其直接所属营销员

type2  B+D：即所有的B级组+B级组直属（即parent_team_id是B级组Id的）D级组 和其直接所属营销员

type3 C+D：即所有的C级组+C级组直属（即parent_team_id是C级组Id的）D级组 和其直接所属营销员

重新切割以后，对type1、type2、type3使用统一标准考核（在这种切割方式之下，各个新分组的实际人数相差不大）

 

处理思想：

首先将营销员汇总到组，然后将D级组累加到其上级，按销售额排序

 

declare @temp table--用于最终存放结果
(
 team_id int primary key,
 parent_team_id int,
 team_money float,
 team_rank char(1)
)

declare @temp_clone table--用于中间运算
(
 team_id int,
 parent_team_id int,
 team_money float,
 team_rank char(1),
 id int identity(1,1) primary key
)

 

insert into @temp_clone
select s.team_id,t.parent_team_id,SUM(_money) as team_money,team_rank
from @sale s
left join @team t on s.team_id = t.team_id
group by s.team_id,t.parent_team_id,team_rank
--order by  t.team_rank desc;--将D类组合并到其上级组

-----

--注意：这个地方会产生一种很特殊的情况

--假设有如下语句

insert into @temp_clone values(101,100,5,'D');
insert into @team values(100,'一组',null,'A');
insert into @team values(101,'一组',100,'D');

--

--注意：这个时候在业绩统计里面，在temp_clone表中并没有组号101的上级（在team定义里有）如果只是做简单向上扫描合并的话，则会丢失上面增加到team_clone表中的数据

--这是个很容易被忽略的地方，理想的做法是在插入team_clone表的时候使用@team开始做左外连接（left join）而不是从@sale开始，这样可以保证所有的组结构都会存在于team_clone中

--本文在没有使用这种处理方式时提出了另外一种解决办法，可以在某些无法从类似team的表发起外连接时使用，且不会遗漏数据

--本文仅在算法上作补遗，不在于开辟新算法

---

 

 

--定义变量
DECLARE
 @team_id int,
 @parent_team_id int,
 @team_money float,
 @team_rank char(1);

declare @current_row int;-- 定义当前行
declare @total_row int;--定义总行数

set @current_row = 1;
select @total_row = COUNT(*) from @temp_clone--取回总行数

WHILE @current_row <= @total_row
BEGIN

 --载入信息
 select
 @team_id =team_id,
 @parent_team_id =parent_team_id,
 @team_money =team_money,
 @team_rank =team_rank
 from @temp_clone
 where id = @current_row --这里看似可以用游标，下面会解释

 

--处理思想，如果当前行不是D组，则将其及所有上级为此行的数据进行金额汇总，并且将计算出的新数据插入到最终结果@temp表中

--如果当前行是D组，则先判断temp_clone中是否具有其上级，如果没有的话，从team表中找到这一样，并向temp_clone插入一个业绩为0的新航

 

 --首先判段是否为D级组
 if(@team_rank = 'D') 
 begin
  if((select COUNT(*)
   from @temp_clone
   where team_id = @parent_team_id)=0)
   begin
    insert into @temp_clone
    select
     team_id,
     parent_team_id,
     0,--业绩为0
     team_rank
    from @team
     where team_id = @parent_team_id
     
    set @total_row = @total_row+1 --总行数加1
   end
 end 
 else
  begin
   insert into @temp
   select
    @team_id,
    @parent_team_id,
    SUM(team_money),
    @team_rank
   from @temp_clone
   where id = @current_row or
   (parent_team_id = @team_id and team_rank = 'D') --这里取出所有D组的上级ID为当前team_id的组
  end
 
 set @current_row  = @current_row +1--行数（游标）加1
 
end

 

select * from @temp--查询最终结果

 

此处不能使用cursor

因为cursor首先会锁定目标的行数，所以无法检测到在游标遍历时新插入到temp_clone的行，所以这里使用伪游标@current_row来实现，将id int identity(1,1)设置为主键也是为了快速遍历

 

 

 代码下载：

 下载