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

with recursive t as 
(
    select 
    c2.cat_id as descendant_id,
    c2.cat_name as descendant_name,
    1 as generation,
    round(br.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 c1.cat_name = 'Luna'
    and br.birth_date >= '2025-01-01' 
    and br.birth_date < '2026-01-01'

    union all
    
    select 
    c.cat_id as descendant_id,
    c.cat_name as descendant_name,
    generation + 1 as generation,
    round(br.health_score * pow(0.95,generation + 1), 2) as composite_index
    from breeding_records br
    join t on t.descendant_id = br.parent_cat_id
    join cats c on c.cat_id = br.child_cat_id
    where br.birth_date >= '2025-01-01' 
    and br.birth_date < '2026-01-01'
)
select * 
from t
order by generation asc,
composite_index desc,
descendant_id asc