with recursive cte as
(
select giver_id, receiver_id, 1 as chain_length, gift_value from secretsanta
union all
select c.giver_id, s.receiver_id, chain_length+1 as chain_length, s.gift_value+c.gift_value as gift_value from cte c
inner join secretsanta s
on c.receiver_id=s.giver_id
where c.giver_id!=c.receiver_id and chain_length<=200 # 题目没有限制这个loop的长度,为了避免无限循环,不知道有没有办法可以限制递归不会重复计算环比如1-2-3-1和2-3-1-2其实是一个,但是这里都会重复计算
)
select rank() over(order by chain_length desc, total_gift_value desc) as chain_id, chain_length, total_gift_value
from
(
select distinct chain_length, gift_value as total_gift_value from cte
where receiver_id=giver_id
group by 1,2 #不过这里有个问题,就是如果有两个不同的环,但是正好有同样的长度和总和,不清楚应该如何区分
)t1