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

with RECURSIVE temp as(
    select 
        child_cat_id as descendant_id,
        c2.cat_name as descendant_name,
        1 as generation,
        health_score AS composite_index
    from breeding_records br
        join cats c on c.cat_id=br.parent_cat_id
        join cats c2 on c2.cat_id=br.child_cat_id
        where c.cat_name='Luna' and birth_date between '2025-01-01' and '2025-12-31'

    union all

    select 
        child_cat_id as descendant_id,
        c.cat_name as descendant_name,
        1+generation ,
        br.health_score as composite_index
    from temp t
        join breeding_records br on t.descendant_id=br.parent_cat_id
        join cats c on c.cat_id=br.child_cat_id
        where  birth_date between '2025-01-01' and '2025-12-31'
)
select 
    descendant_id,
    descendant_name,
    generation,
    round(composite_index * power( 0.95 ,  generation), 2) as composite_index
from temp
order by generation,composite_index desc,descendant_id;