题解 | 宠物猫繁育族谱追溯与遗传病风险评估

WITH RECURSIVE t AS (
    SELECT c2.cat_id AS descendant_id,
           c2.cat_name AS descendant_name,
           1 AS generation,
           ROUND(health_score*pow(0.95,1),2) AS composite_index
    FROM breeding_records br 
    JOIN cats c1 ON br.parent_cat_id=c1.cat_id
    JOIN cats c2 ON br.child_cat_id=c2.cat_id
    WHERE YEAR(birth_date)=2025 AND c1.cat_name='Luna'

    UNION ALL
    SELECT c2.cat_id AS descendant_id,
           c2.cat_name AS descendant_name,
           generation+1,
           ROUND(health_score*pow(0.95,generation+1),2) AS composite_index
    FROM t 
    JOIN breeding_records br ON br.parent_cat_id=t.descendant_id
    JOIN cats c2 ON br.child_cat_id=c2.cat_id
    WHERE YEAR(birth_date)=2025 
    )
SELECT *
FROM t 
ORDER BY generation ASC,composite_index DESC,descendant_id ASC