题解 | 宠物猫繁育族谱追溯与遗传病风险评估 使用recursive 就可以解决

with recursive cte as(
    select
        b.child_cat_id as descendant_id,
        c1.cat_name as descendant_name,
        1 as depth,
        b.health_score * power(0.95,1) as score
    from
        cats c
    join 
        breeding_records b on b.parent_cat_id = c.cat_id
    join 
        cats c1 on c1.cat_id = b.child_cat_id
    where
        c.cat_name='Luna'

    union all

        select
            b.child_cat_id,
            c1.cat_name as descendant_name,
            c.depth+1 as depth,
            round(b.health_score * power(0.95,c.depth+1),2) as score
        from
            breeding_records b
        join 
            cte c on b.parent_cat_id = c.
descendant_id
        join 
            cats c1 on b.child_cat_id = c1.cat_id
        where
            b.birth_date >= '2025-01-01' and birth_date<'2026-01-01' 
)

select
    c.descendant_id,
    c.descendant_name,
    c.depth as generation,
    c.score as composite_index
from
    cte c
order by 
    generation,composite_index desc,descendant_id