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

with recursive descendant_tree as(
select child_cat_id as descendant_id
,1 as generation
,health_score
from breeding_records 
where parent_cat_id = (select cat_id from cats where cat_name='Luna')
and year(birth_date) = '2025'

UNION ALL

select br.child_cat_id
,dt.generation+1 generation
,br.health_score
from descendant_tree dt inner join breeding_records br
on dt.descendant_id=br.parent_cat_id 
where year(br.birth_date) = '2025'

)
select dt.descendant_id
,c.cat_name descendant_name
,dt.generation generation
,round(dt.health_score*pow(0.95,dt.generation),2) composite_index
from descendant_tree dt
left join cats c on dt.descendant_id=c.cat_id
order by generation asc ,composite_index desc,descendant_id asc