34,588
社区成员
发帖
与我相关
我的任务
分享
场景描述:一个优惠券的功能。A可以转发给B,B可以转发给C,。。。。如果C没有使用,将返回给A(最高父级节点)
主键 券接收者 发放主键 可用张数 状态 父级主键
1 A 421 1 0 422
2 B 421 1 0 423
3 C 423 1 0 424
4 D 424 1 0 425
5 D 421 1 0 427
6 D 421 0 0 428
说明:第1条数据表示 转发给A的,如果A没有使用,需要返回给转发者,这里把可用张数更新为0,同时记录下父级主键422,保存到一个表中.
第2-4条数据是一个转发链,主要看发放主键和父级主键的关系,把这3条数据的父级主键 记录下来保存到一个表中,同时把可用张数更新为0,如果第4条数据的可用张数为0.第2-4数据都不用考虑了,因为D已经使用了券,不用返回。
第5条数据和第一条类似,保存427即可。
第6条数据的可用张数为0,说明已经使用了,不用考虑。
所有要的结果应该是一个表
-----想要的结果
父级主键
422
423
424
425
427
然后表的记录改变为:
主键 券接收者 发放主键 可用张数 状态 父级主键
1 A 421 0 0 422
2 B 421 0 0 423
3 C 423 0 0 424
4 D 424 0 0 425
5 D 421 0 0 427
6 D 421 0 0 428
----测试数据
create table tb
(
主键 int identity(1,1) primary key,
券接收者 nvarchar(50),
发放主键 int,
可用张数 int,
状态 int,
父级主键 int
)
insert into tb
select
1, 'A', 421, 1, 0, 422 union all select
2, 'B', 421, 1, 0, 423 union all select
3, 'C', 423, 1, 0, 424 union all select
4, 'D', 424, 1, 0, 425 union all select
5, 'D', 421, 1, 0, 427 union all select
6, 'D', 421, 0, 0, 428
WITH ctb(主键,券接收者,发放主键,可用张数,父级主键,顶级主键,Lvl)
AS( /* 同20楼 */ )
SELECT r.父级主键
FROM ctb r
WHERE r.Lvl = 0
AND r.可用张数=1 -- 自己没用掉
AND NOT EXISTS (SELECT * -- 后面的人也没用掉
FROM ctb c
WHERE c.顶级主键 = r.顶级主键
AND c.Lvl > 0
AND c.可用张数 = 0)
初始测试数据结果
父级主键
-----------
422
425
427
第3条用掉了的结果
父级主键
-----------
422
427
发放主键,父级主键
423,424
WITH ctb(主键,券接收者,发放主键,可用张数,父级主键,顶级主键,Lvl)
AS( -- 传递链的第一人
SELECT 主键,券接收者,发放主键,可用张数,父级主键,父级主键,0
FROM tb
WHERE NOT EXISTS (SELECT *
FROM tb p
WHERE p.发放主键 = tb.父级主键)
UNION ALL
-- 全部传递链的人都包含进来
SELECT tb.主键,
tb.券接收者,
tb.发放主键,
tb.可用张数 * ctb.可用张数, -- 只有 0/1 就不用 CASE WHEN 了
tb.父级主键,
ctb.顶级主键,
ctb.Lvl+1
FROM tb, ctb
WHERE ctb.发放主键 = tb.父级主键
)
SELECT *
FROM ctb
ORDER BY 顶级主键,Lvl
还是初始测试数据
主键 券接收者 发放主键 可用张数 状态 父级主键 顶级主键 Lvl
----------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
1 A 421 1 0 422 422 0
4 D 424 1 0 425 425 0
3 C 423 1 0 424 425 1
2 B 421 1 0 423 425 2
5 D 421 1 0 427 427 0
6 D 421 0 0 428 428 0
我把第3条数据的可用张数设为0更有代表性
主键 券接收者 发放主键 可用张数 状态 父级主键 顶级主键 Lvl
----------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
1 A 421 1 422 422 0
4 D 424 1 425 425 0
3 C 423 0 424 425 1
2 B 421 0 423 425 2
5 D 421 1 427 427 0
6 D 421 0 428 428 0