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

with recursive descendant_tree as (
    select      -- 首先找到Luan的锚点猫
    child_cat_id as descendant_id,
    1 as generation ,  --把它当作一代猫
    health_score
    from breeding_records
    where year(birth_date) = '2025' and parent_cat_id in (select cat_id from cats where cat_name = 'Luna')

    union all        -- 两张表连接起来

    select             -- 迭代部分  
    br.child_cat_id,   -- 找子猫的id
    dt.generation + 1 as generation ,  -- 每找到一次，代数加1
    br.health_score
    from descendant_tree dt 
    inner join breeding_records br
    on br.parent_cat_id = dt.descendant_id          -- 把找到的子猫继续当作父猫，继续找下一代
    where year(br.birth_date) = '2025'
)   -- 以上部分为构建递归查询
select dt.descendant_id,
       c.cat_name as descendant_name,
       dt.generation,
       round(dt.health_score*pow(0.95,dt.generation),2) composite_index
       from descendant_tree dt
       left join cats c
       on c.cat_id = dt.descendant_id
       order by generation asc , composite_index desc , descendant_id asc;