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

WITH RECURSIVE t AS (
    SELECT m2.member_id AS referee_id,
           m2.member_name AS referee_name,
           1 AS referral_level,
          ROUND(base_reward_points*POW(0.5,0),2)  AS actual_points
    FROM referral_records r 
    JOIN members m1 ON r.referrer_id=m1.member_id
    JOIN members m2 ON r.referee_id=m2.member_id
    WHERE join_date BETWEEN '2025-01-01' AND '2025-06-30' AND m1.member_name='张三'

    UNION ALL 

    SELECT m2.member_id AS referee_id,
           m2.member_name AS referee_name,
           referral_level+1 ,
          ROUND(base_reward_points*POW(0.5,referral_level),2)  AS actual_points
    FROM t
    JOIN referral_records r ON r.referrer_id=t.referee_id
    JOIN members m2 ON r.referee_id=m2.member_id
    WHERE join_date BETWEEN '2025-01-01' AND '2025-06-30'   
)
SELECT *
FROM t
ORDER BY referral_level ASC,actual_points DESC,referee_id ASC