MySQL带WHILE循环查询：基于related_id计算祖先树层级

要实现这个需求，我们可以通过存储过程+临时表+WHILE循环的方式逐步计算每个节点的向下层级。因为普通SELECT语句无法直接嵌入WHILE循环，存储过程是比较合适的选择，下面是具体实现：

1. 创建存储过程 #

DELIMITER //

CREATE PROCEDURE CalculateAncestorLevels()
BEGIN
    -- 创建临时表存储结果，包含id、related_id和层级level
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_levels (
        id INT,
        related_id INT,
        level INT,
        PRIMARY KEY (id)
    );

    -- 先插入所有根节点（related_id=0），层级设为0
    INSERT INTO temp_levels (id, related_id, level)
    SELECT id, related_id, 0
    FROM ancestors
    WHERE related_id = 0;

    -- 定义变量记录每次插入的行数，用于判断循环是否继续
    DECLARE inserted_rows INT;
    SET inserted_rows = 1;

    -- WHILE循环：直到没有新节点插入时停止
    WHILE inserted_rows > 0 DO
        -- 插入父节点已在临时表中的子节点，层级为父节点层级+1
        INSERT INTO temp_levels (id, related_id, level)
        SELECT a.id, a.related_id, tl.level + 1
        FROM ancestors a
        JOIN temp_levels tl ON a.related_id = tl.id
        WHERE a.id NOT IN (SELECT id FROM temp_levels);

        -- 获取本次插入的行数，判断是否继续循环
        SET inserted_rows = ROW_COUNT();
    END WHILE;

    -- 查询最终结果
    SELECT id, related_id, level
    FROM temp_levels
    ORDER BY related_id, level;

    -- 可选：删除临时表（会话结束后会自动删除）
    DROP TEMPORARY TABLE IF EXISTS temp_levels;
END //

DELIMITER ;

2. 调用存储过程并查看结果 #

执行以下语句调用存储过程：

CALL CalculateAncestorLevels();

根据你提供的表数据，最终输出结果会是：

3. 逻辑说明 #

• 临时表初始化：先把所有根节点（related_id=0）加入临时表，层级设为0，这是整个树的起点。
• WHILE循环逻辑：每次循环都会找出所有父节点已经被处理过（存在于临时表中）的子节点，计算它们的层级为父节点层级+1，然后插入临时表。循环会一直执行，直到某次没有新节点插入（inserted_rows=0），说明所有节点都已处理完毕。
• 结果排序：最后按related_id和level排序，方便查看整个祖先树的层级结构。

如果你不想用存储过程，MySQL 8.0及以上版本也支持递归CTE（不需要WHILE循环），不过既然你明确要求WHILE循环，上面的存储过程方案就完全符合需求啦。

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