如何在MySQL父子层级查询中添加层级字段（最多7级）

Got it, let's figure out how to add that level column to your hierarchical query. You want to track how many steps each member is from the root (id=1), with a max of 7 levels. Here are two approaches—one modifying your original variable-based query, and a cleaner alternative if you're on a newer MySQL version.

Modifying Your Original Query #

Your existing query uses a variable @pv to track discovered node IDs. We'll add a second variable to track each node's level, storing pairs of id:level as a string. Here's the adjusted SQL:

SELECT 
    id AS member_id, 
    name, 
    -- Pull the parent's level and add 1 for the current node's level
    SUBSTRING_INDEX(SUBSTRING_INDEX(@level_tracker, CONCAT(introducer_id, ':'), -1), ',', 1) + 1 AS level
FROM (
    SELECT * FROM table_member ORDER BY introducer_id, id
) table_member_sorted,
-- Initialize root node (id=1) with level 0 (its children will be level 1)
(SELECT @pv := '1', @level_tracker := '1:0') initialisation
WHERE 
    FIND_IN_SET(introducer_id, @pv) 
    -- Update the list of found member IDs
    AND LENGTH(@pv := CONCAT(@pv, ',', id))
    -- Update the level tracker with the current node's ID and calculated level
    AND LENGTH(@level_tracker := CONCAT(
        @level_tracker, 
        ',', 
        id, ':', 
        (SUBSTRING_INDEX(SUBSTRING_INDEX(@level_tracker, CONCAT(introducer_id, ':'), -1), ',', 1) + 1)
    ))
    -- Enforce max 7 levels as requested
    AND (SUBSTRING_INDEX(SUBSTRING_INDEX(@level_tracker, CONCAT(introducer_id, ':'), -1), ',', 1) + 1) <= 7;

How This Works:

1. We start by initializing @level_tracker with '1:0'—this marks the root node (id=1) as level 0, so its direct reports will be level 1.
2. For each matching member, we extract their parent's level from @level_tracker, add 1 to get their own level.
3. We update both @pv (to track new members) and @level_tracker (to store the new member's level) in the WHERE clause.
4. The final condition ensures we stop at level 7.

Cleaner Alternative: Recursive CTE (MySQL 8.0+) #

If you're using MySQL 8.0 or newer, recursive Common Table Expressions (CTEs) are way more readable and easier to maintain. Here's how to do it:

WITH RECURSIVE member_hierarchy AS (
    -- Anchor: Start with the root node (id=1) at level 0
    SELECT 
        id, 
        introducer_id, 
        name, 
        0 AS level
    FROM table_member
    WHERE id = 1
    UNION ALL
    -- Recursive step: Join child nodes to their parents, increment level by 1
    SELECT 
        tm.id, 
        tm.introducer_id, 
        tm.name, 
        m.level + 1 AS level
    FROM table_member tm
    JOIN member_hierarchy m ON tm.introducer_id = m.id
    -- Stop at level 7
    WHERE m.level + 1 <= 7
)
-- Exclude the root node, show only its descendants
SELECT 
    id AS member_id, 
    name, 
    level
FROM member_hierarchy
WHERE level > 0
ORDER BY level, id;

How This Works:

1. The anchor part selects the root node and sets its level to 0.
2. The recursive part repeatedly joins child members to their parent entries in the CTE, adding 1 to the parent's level for each child.
3. We filter out the root node at the end and sort by level (then ID) to match your desired output.

Both queries will produce exactly the result you want:

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