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

with recursive generat_table as (
    select a.child_cat_id descendant_id,a.child_cat_name descendant_name,1 as generation,ROUND(a.health_score * POW(0.95, 1), 2) AS composite_index from  (select a.*,b.cat_name parent_cat_name,c.cat_name child_cat_name from    breeding_records a left join cats b on a.parent_cat_id = b.cat_id 
    left join cats c on a.child_cat_id = c.cat_id 
    ) a where a.parent_cat_name = 'Luna' and a.birth_date >= '2025-01-01' and a.birth_date <= '2025-12-31'

    union all

    select a.child_cat_id,a.child_cat_name,b.generation+1,ROUND(a.health_score * POW(0.95, (b.generation + 1)), 2) AS composite_index from generat_table b left join  (select a.*,b.cat_name parent_cat_name,c.cat_name child_cat_name from    breeding_records a left join cats b on a.parent_cat_id = b.cat_id 
    left join cats c on a.child_cat_id = c.cat_id 
    ) a on b.descendant_id = a.parent_cat_id
     where a.birth_date >= '2025-01-01' and a.birth_date <= '2025-12-31'
)
select * from generat_table
order by generation, composite_index desc,descendant_id