题解 | 全民健身季推荐网络与积分衰减计算

WITH recursive tmp AS(
    
-- 查第一次推荐的
SELECT rr.referee_id, m.member_name referee_name, 
    1 referral_level,
    rr.base_reward_points
FROM referral_records rr 
JOIN members m ON rr.referee_id = m.member_id
WHERE rr.referrer_id = (
    -- 查张三
    SELECT member_id
    FROM members
    WHERE member_name = '张三'
) AND join_date BETWEEN ' 2025-01-01' AND '2025-06-30'
UNION ALL
-- 开始递归，寻找下一层
SELECT rr.referee_id, m.member_name,
    referral_level + 1,
    rr.base_reward_points
FROM tmp t
JOIN  referral_records rr ON t.referee_id = rr.referrer_id
JOIN members m ON rr.referee_id = m.member_id
WHERE join_date BETWEEN ' 2025-01-01' AND '2025-06-30'
)
SELECT referee_id, referee_name, referral_level,
    ROUND((base_reward_points)*POW(0.5,referral_level-1),2) actual_points
FROM tmp 
ORDER BY referral_level, actual_points DESC, referee_id