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

WITH recursive temp AS(
    SELECT  br.child_cat_id AS descendant_id,
        c.cat_name AS descendant_name,
        1 AS generation,
        br.health_score,
        br.birth_date
    FROM breeding_records br
    JOIN cats c ON br.child_cat_id = c.cat_id
    WHERE br.parent_cat_id = (SELECT cat_id FROM cats WHERE cat_name = 'Luna')
        AND br.birth_date BETWEEN '2025-01-01' AND '2025-12-31'


    UNION ALL
    SELECT br.child_cat_id, c.cat_name, t.generation + 1, br.health_score, br.birth_date
    FROM temp t
    JOIN breeding_records br ON br.parent_cat_id = t.descendant_id
    JOIN cats c ON c.cat_id =  br.child_cat_id
    AND br.birth_date BETWEEN '2025-01-01' AND '2025-12-31'
)
SELECT descendant_id, descendant_name, generation, ROUND(health_score*POWER(0.95,generation),2)composite_index
FROM temp
ORDER BY 3, 4 DESC,1