请大侠赐教,无限级树形结构,查某父节点下的所有子节点,并按某条件提取子节点的数值。

Liuren_flf 2017-06-09 12:50:19
最近接手一个直销公司的奖金制度设计,想写个存储过程把某节点当月的奖金算出,当初以为不难,结果仔细一想,花容失色,感觉好大的难度,烦请大侠赐教!

先上结构示意图:


建立测试数据表代码:

create table tb_test(
id int not null, --主键id
title varchar(50), --节点名
parent int, --parent id
lvl int, --节点自身级别
sales int --节点销售额
);

--第一层
insert into tb_test(id, title, parent,lvl,sales) values(1, 'A点',null,4,1000); --A节点
insert into tb_test(id, title, parent,lvl,sales) values(2, 'B点',1,3,1200); --B节点
insert into tb_test(id, title, parent,lvl,sales) values(3, 'C点',1,0,100); --C节点
insert into tb_test(id, title, parent,lvl,sales) values(4, 'D点',1,0,120); --D节点
insert into tb_test(id, title, parent,lvl,sales) values(5, 'E点',1,0,110); --E节点

--第二层
insert into tb_test(id, title, parent,lvl,sales) values(6, 'F点',2,0,140); --F节点
insert into tb_test(id, title, parent,lvl,sales) values(7, 'G点',2,0,110); --G节点
insert into tb_test(id, title, parent,lvl,sales) values(8, 'H点',2,0,120); --H节点

insert into tb_test(id, title, parent,lvl,sales) values(9, 'I点',3,0,120); --I节点
insert into tb_test(id, title, parent,lvl,sales) values(10, 'J点',5,0,100); --J节点

--第三层
insert into tb_test(id, title, parent,lvl,sales) values(11, 'K点',9,5,100); --K节点
insert into tb_test(id, title, parent,lvl,sales) values(12, 'R点',10,0,1100); --R节点

--第四层
insert into tb_test(id, title, parent,lvl,sales) values(13, 'L点',11,0,1100); --L节点
insert into tb_test(id, title, parent,lvl,sales) values(14, 'M点',11,0,100); --M节点
insert into tb_test(id, title, parent,lvl,sales) values(15, 'N点',11,0,110); --N节点
insert into tb_test(id, title, parent,lvl,sales) values(16, 'O点',11,0,110); --O节点
insert into tb_test(id, title, parent,lvl,sales) values(17, 'P点',11,0,130); --P节点

--第五层
insert into tb_test(id, title, parent,lvl,sales) values(18, 'Q点',15,0,100); --Q节点

--无限层--



奖金制度:

A可以提取下面所有子节点销售额的1%,但当有子节点的级别等于或高于A时,比如K的级别高于A,那么K节点及以下所有销售额与A无关,即A无法从K的团队提取奖金。因为K自己要提取下面子节点的提成。

另外比如B的级别是3,比A的级别低,那么A可以提取B团队下的0.5%,而不是1%。


最终想要的结果,A到底可以得到多少奖金?




...全文
331 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
Liuren_flf 2017-06-10
  • 打赏
  • 举报
回复
非常感谢两位的帮助,结贴。
zhouyuehai1978 2017-06-10
  • 打赏
  • 举报
回复
;WITH a AS (
SELECT tt.*,0 AS newlv FROM tb_test AS tt WHERE tt.title='A点'
UNION ALL
SELECT tt.*,a.newlv+1 AS newlv FROM tb_test AS tt JOIN a ON tt.parent=a.id
)
SELECT SUM(
CASE
WHEN title = 'A点' THEN sales
WHEN newlv = 1 THEN sales * 0.03
WHEN newlv = 2 THEN sales * 0.02
WHEN newlv = 3 THEN sales * 0.01
ELSE 0
END
) AS totalsales
FROM a
OPTION(MAXRECURSION 0)

二月十六 2017-06-09
  • 打赏
  • 举报
回复
;WITH ctea AS(
SELECT * ,
1 AS ll
FROM tb_test
WHERE parent IS NULL
UNION ALL
SELECT a.* ,
b.ll+1 AS ll
FROM dbo.tb_test a
JOIN ctea b ON a.parent = b.id
)
SELECT SUM(CASE WHEN ll = 1 THEN ctea.sales
WHEN ll = 2 THEN ctea.sales * 0.03
WHEN ll = 3 THEN ctea.sales * 0.02
WHEN ll = 4 THEN ctea.sales * 0.01
ELSE 0
END)
FROM ctea;


Liuren_flf 2017-06-09
  • 打赏
  • 举报
回复
引用 8 楼 sinat_28984567 的回复:
另外比如B的级别是3,比A的级别低,那么A可以提取B团队下的0.5%,而不是1%。 这个条件不变是吗?
提取它下面三层的奖金,不用考虑级别了。与级别无关,只与子节点所在的层有关。 提取: 第一层人员销售额的3% 第二层人员销售额的2% 第三层人员销售额的1%,
二月十六 2017-06-09
  • 打赏
  • 举报
回复
另外比如B的级别是3,比A的级别低,那么A可以提取B团队下的0.5%,而不是1%。 这个条件不变是吗?
Liuren_flf 2017-06-09
  • 打赏
  • 举报
回复
谢谢“sinat_28984567 ”和 “zhouyuehai1978”两位。再追问一下。 只允许A提取它下面的1~3层人员的销售额。 提取第一层人员销售额的3% 第二层人员销售额的2% 第三层人员销售额的1%, 应该怎么写,这个应该是需要先把第一层,第二层,第三层的人员找出来,然后按不同层级来实现抽成比例?
zhouyuehai1978 2017-06-09
  • 打赏
  • 举报
回复
--先找到A下面所有的子节点
;WITH a AS (
SELECT tt.* FROM tb_test AS tt WHERE tt.title='A点'
UNION ALL
SELECT tt.* FROM tb_test AS tt JOIN a ON tt.parent=a.id
)
SELECT SUM(
CASE
WHEN title = 'A点' THEN sales
WHEN lvl = 4 THEN sales * 0.01
WHEN lvl < 4 THEN sales * 0.005
WHEN lvl > 4 THEN 0
END
) AS totalsales
FROM a
OPTION(MAXRECURSION 0)

zhouyuehai1978 2017-06-09
  • 打赏
  • 举报
回复
另外,为什么是“花容失色”
zhouyuehai1978 2017-06-09
  • 打赏
  • 举报
回复
楼主的描述感觉不是很清晰,整理下: A可以提取其下所有级别小于等于A的销售额 同级别的提取1%,非同级别的提取0.5%是这个意思吗?
二月十六 2017-06-09
  • 打赏
  • 举报
回复
最后计算有的问题,取得是1和0.5没有加百分比
;WITH ctea AS(
SELECT * ,
1.0 AS [percent]
FROM tb_test
WHERE parent IS NULL
UNION ALL
SELECT a.* ,
( CASE WHEN a.lvl <> 0 THEN 0.5 --自己有lvl的设置成0.5 自己没有lvl的继承父级的
ELSE b.[percent] --自己没有
END ) AS ll
FROM dbo.tb_test a
JOIN ctea b ON a.parent = b.id
WHERE a.lvl < 4 --去掉比A级别高的
)
SELECT ( SELECT sales
FROM ctea
WHERE title = 'A点'
) + SUM(ctea.sales * ctea.[percent] * 0.01)
FROM ctea


Liuren_flf 2017-06-09
  • 打赏
  • 举报
回复
谢谢“sinat_28984567”,非常感谢, 我先看一下,我怕自己的没有表述清楚。我先测试一下数据。
二月十六 2017-06-09
  • 打赏
  • 举报
回复
;WITH ctea AS(
SELECT * ,
1.0 AS [percent]
FROM tb_test
WHERE parent IS NULL
UNION ALL
SELECT a.* ,
( CASE WHEN a.lvl <> 0 THEN 0.5 --自己有lvl的设置成0.5 自己没有lvl的继承父级的
ELSE b.[percent] --自己没有
END ) AS ll
FROM dbo.tb_test a
JOIN ctea b ON a.parent = b.id
WHERE a.lvl < 4 --去掉比A级别高的
)
SELECT SUM(sales*[percent]) AS sumsales FROM ctea


22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧