如何编写MySQL存储过程实现马匹系谱（家族树）单条记录展示

• 马匹系谱属于典型的递归查询场景，常规赛马系谱默认展示5代，总计包含1（目标马）+2+4+8+16=31个亲属节点
• 你可以通过递归CTE（MySQL8.0+支持）或者存储过程循环向上查询每一代的父系（SireID）和母系（DamID），记录每个个体的代际和位置编码
• 最后通过条件聚合将不同代的个体信息按固定位置拼装为单行数据，匹配你需要的单行列展示格式

无需创建存储过程，直接执行查询即可返回指定马匹的系谱单行数据：

WITH RECURSIVE pedigree AS (
    -- 锚点：查询目标马匹（第0代）
    SELECT 
        horse_id, 
        horse_name, 
        SireID, 
        DamID, 
        0 AS generation,
        1 AS position -- 位置编码规则：父系为当前位置*2，母系为当前位置*2+1
    FROM horses 
    WHERE horse_id = 1 -- 替换为你要查询的目标马匹ID
    UNION ALL
    -- 递归查询父代
    SELECT 
        h.horse_id,
        h.horse_name,
        h.SireID,
        h.DamID,
        p.generation + 1 AS generation,
        p.position * 2 AS position
    FROM pedigree p
    JOIN horses h ON p.SireID = h.horse_id
    WHERE p.generation < 4 -- 控制查询到第4代，总共覆盖5代信息
    UNION ALL
    -- 递归查询母代
    SELECT 
        h.horse_id,
        h.horse_name,
        h.SireID,
        h.DamID,
        p.generation + 1 AS generation,
        p.position * 2 + 1 AS position
    FROM pedigree p
    JOIN horses h ON p.DamID = h.horse_id
    WHERE p.generation < 4
)
-- 聚合为单行展示
SELECT
    MAX(IF(position = 1, horse_name, NULL)) AS `目标马`,
    MAX(IF(position = 2, horse_name, NULL)) AS `父系`,
    MAX(IF(position = 3, horse_name, NULL)) AS `母系`,
    MAX(IF(position = 4, horse_name, NULL)) AS `祖父`,
    MAX(IF(position = 5, horse_name, NULL)) AS `祖母`,
    MAX(IF(position = 6, horse_name, NULL)) AS `外祖父`,
    MAX(IF(position = 7, horse_name, NULL)) AS `外祖母`,
    -- 可按需求继续扩展到第5代的16个节点，对应position 8到31即可
    MAX(IF(position = 8, horse_name, NULL)) AS `曾祖父1`,
    MAX(IF(position = 9, horse_name, NULL)) AS `曾祖母1`,
    MAX(IF(position = 10, horse_name, NULL)) AS `曾祖父2`,
    MAX(IF(position = 11, horse_name, NULL)) AS `曾祖母2`,
    MAX(IF(position = 12, horse_name, NULL)) AS `曾祖父3`,
    MAX(IF(position = 13, horse_name, NULL)) AS `曾祖母3`,
    MAX(IF(position = 14, horse_name, NULL)) AS `曾祖父4`,
    MAX(IF(position = 15, horse_name, NULL)) AS `曾祖母4`
FROM pedigree;

如果你的数据库版本不支持CTE，可以使用以下存储过程实现：

DELIMITER //
CREATE PROCEDURE GetHorsePedigree(IN target_horse_id INT, IN max_generation INT)
BEGIN
    -- 创建临时表存储系谱中间数据
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_pedigree (
        horse_id INT,
        horse_name VARCHAR(32),
        SireID INT,
        DamID INT,
        generation INT,
        position INT
    );
    TRUNCATE TABLE temp_pedigree;
    
    -- 插入目标马基础数据
    INSERT INTO temp_pedigree 
    SELECT horse_id, horse_name, SireID, DamID, 0, 1 FROM horses WHERE horse_id = target_horse_id;
    
    -- 循环向上查询每一代亲属
    SET @current_gen = 0;
    WHILE @current_gen < max_generation DO
        -- 插入父代
        INSERT INTO temp_pedigree
        SELECT h.horse_id, h.horse_name, h.SireID, h.DamID, @current_gen + 1, p.position * 2
        FROM temp_pedigree p
        JOIN horses h ON p.SireID = h.horse_id
        WHERE p.generation = @current_gen;
        -- 插入母代
        INSERT INTO temp_pedigree
        SELECT h.horse_id, h.horse_name, h.SireID, h.DamID, @current_gen + 1, p.position * 2 + 1
        FROM temp_pedigree p
        JOIN horses h ON p.DamID = h.horse_id
        WHERE p.generation = @current_gen;
        
        SET @current_gen = @current_gen + 1;
    END WHILE;
    
    -- 输出聚合后的单行系谱，字段规则和CTE方案一致
    SELECT
        MAX(IF(position = 1, horse_name, NULL)) AS `目标马`,
        MAX(IF(position = 2, horse_name, NULL)) AS `父系`,
        MAX(IF(position = 3, horse_name, NULL)) AS `母系`,
        MAX(IF(position = 4, horse_name, NULL)) AS `祖父`,
        MAX(IF(position = 5, horse_name, NULL)) AS `祖母`,
        MAX(IF(position = 6, horse_name, NULL)) AS `外祖父`,
        MAX(IF(position = 7, horse_name, NULL)) AS `外祖母`
        -- 按需扩展更多代字段
    FROM temp_pedigree;
    
    DROP TEMPORARY TABLE IF EXISTS temp_pedigree;
END //
DELIMITER ;

• 调用存储过程查询指定马匹5代系谱：CALL GetHorsePedigree(目标马匹ID, 4); 第二个参数4代表查询到第4代，总计展示5代信息
• 如果部分马匹的父/母ID为空，对应字段会返回NULL，你可以用IFNULL(字段名, '无记录')替换默认返回值
• 如需展示更多马匹属性（比如出生日期、血统编号等），只需在临时表定义和查询逻辑中新增对应字段即可

内容的提问来源于stack exchange，提问作者Ritesh Rohan