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

# 查出由名为'Luna'的宠物猫繁育出的所有直接和间接后代。


with recursive descendant_tree as(
    # 初始查询 查询名为'Luna'的宠物猫繁育出的所有直接后代
    select 
        child_cat_id as descendant_id,
        1 as generation,# 其中直接子女的世代代数为1
        health_score
    from breeding_records 
    where parent_cat_id = (
        select cat_id from cats where cat_name = 'Luna')
        and year(birth_date) = 2025
        # 仅统计出生日期（birth_date）在 2025年1月1日至2025年12月31日期间的后代

    union all

    # 递归查询 查询名为'Luna'的宠物猫繁育出的所有间接后代
    select 
        b.child_cat_id as descendant_id,
        d.generation + 1 as generation, # 其中直接子女的世代代数为1，孙辈为2，以此类推。
        b.health_score
    from breeding_records b
    inner join descendant_tree d 
    on b.parent_cat_id = d.descendant_id
    where year(b.birth_date) = 2025

)

select 
    d.descendant_id,
    c.cat_name as descendant_name,
    d.generation,
    round(d.health_score*power(0.95,d.generation),2) as composite_index
    # 综合健康评估指数（四舍五入保留2位小数） 
    # 计算公式为：基础健康评分(health_score) * (0.95 ^ 世代代数)
    # power()幂运算
from descendant_tree d
left join cats c
on d.descendant_id = c.cat_id
order by generation,composite_index desc,descendant_id;
# 排序规则：
# 结果须首先按世代代数（generation）进行升序排列；若世代代数相同，则按综合健康评估指数（composite_index）进行降序排列；若综合健康评估指数也相同，则按后代猫的注册编号（descendant_id）进行升序排列。