最近做一个小工作，为公司销售队伍分配旧的保单，孤儿单分配给对应的增援者。主要工作是从tmp_gx_ag里面寻找对应人员的增员者，如果该增员者为辞退，则找增员者的增员者，简单来说就是一个递归关系。代码如下：
|
;
with zyCTE(ag_code,zy_code,state)
as
(
select b.ag_code,b.zy_code,b.state
from tmp_gx_ag as b
where b.ag_code = '44010000001834' --递归结束，可用@ag_code变量代替
union all
select d.ag_code,d.zy_code,d.state
from zyCTE as c inner join tmp_gx_ag as d on d.ag_code = c.zy_code
)
select top(1)* --返回血缘关系最近的增员者
from zyCTE
where state = '聘用'
|
阅读(394) | 评论(0) | 转发(0) |
