求编写MySQL递归查询语句：获取指定ID下属所有层级用户

针对你的需求——从users表中获取指定ID用户添加的所有下属，包括下属的下属等所有层级关联用户，下面分两种MySQL版本给出实现方案：

MySQL 8.0+ 递归CTE实现 #

MySQL 8.0及以上版本支持递归公共表表达式（CTE），这是实现层级查询最直观的方式：

WITH RECURSIVE user_hierarchy AS (
    -- 初始查询：获取指定ID直接添加的用户
    SELECT id, added_by_id
    FROM users
    WHERE added_by_id = 1 -- 这里替换为你要查询的指定ID
    UNION ALL
    -- 递归查询：获取下属的下属，直到没有更深层级
    SELECT u.id, u.added_by_id
    FROM users u
    JOIN user_hierarchy uh ON u.added_by_id = uh.id
)
SELECT id FROM user_hierarchy;

说明 #

• 把WHERE added_by_id = 1中的1替换成你需要查询的目标ID即可，比如查询ID2的下属就改成2。
• 递归CTE会自动遍历所有层级的下属，不需要手动处理多层关联。

MySQL 5.x 自定义函数实现 #

如果你的MySQL版本低于8.0，不支持CTE，可以通过自定义函数来实现递归查询：

首先创建一个递归函数：

DELIMITER //
CREATE FUNCTION get_all_subordinates(parent_id INT)
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE result TEXT DEFAULT '';
    DECLARE temp_ids TEXT;
    -- 先获取直接下属
    SELECT GROUP_CONCAT(id SEPARATOR ',') INTO temp_ids
    FROM users WHERE added_by_id = parent_id;
    
    IF temp_ids IS NOT NULL THEN
        SET result = temp_ids;
        -- 递归获取每个下属的下属
        DECLARE cur CURSOR FOR SELECT id FROM users WHERE added_by_id = parent_id;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1;
        DECLARE sub_id INT;
        SET @done = 0;
        OPEN cur;
        read_loop: LOOP
            FETCH cur INTO sub_id;
            IF @done THEN
                LEAVE read_loop;
            END IF;
            SET result = CONCAT(result, ',', get_all_subordinates(sub_id));
        END LOOP;
        CLOSE cur;
    END IF;
    RETURN result;
END //
DELIMITER ;

然后调用函数查询：

-- 查询ID1的所有下属，返回用逗号分隔的ID列表
SELECT get_all_subordinates(1) AS subordinate_ids;

-- 如果需要拆分每行显示一个ID，可以用下面的语句
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(subordinate_ids, ',', n), ',', -1) AS id
FROM (SELECT get_all_subordinates(1) AS subordinate_ids) t
JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) numbers
ON CHAR_LENGTH(subordinate_ids) - CHAR_LENGTH(REPLACE(subordinate_ids, ',', '')) >= n - 1;

说明 #

• 调用函数时传入目标ID即可，比如get_all_subordinates(2)就是查询ID2的所有下属。
• 第二个查询语句中的数字列表（SELECT 1 n UNION ALL ...）需要根据实际可能的下属数量调整，确保覆盖所有层级的用户数。

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