MySQL如何查询group_id为1、2下的所有层级子组数据

适用版本：MySQL 8.0及以上（支持递归CTE） #

直接使用以下查询语句，将你的表名替换为实际表名即可，支持在IN条件中传入任意多个根节点ID：

WITH RECURSIVE group_hierarchy AS (
    -- 锚点：查询一级子组（直接归属根节点1、2的分组）
    SELECT id, name, group_id 
    FROM 你的表名 
    WHERE group_id IN (1, 2)
    UNION ALL
    -- 递归：查询下一级子组
    SELECT t.id, t.name, t.group_id
    FROM 你的表名 t
    INNER JOIN group_hierarchy gh ON t.group_id = gh.id
)
SELECT * FROM group_hierarchy;

扩展说明 #

• 如果需要同时返回根节点（id=1的income、id=2的expenses），只需将锚点部分的WHERE条件替换为WHERE id IN (1, 2)即可
• 该查询可以返回所有层级的子组，不受层级深度限制
• 如果需要过滤重复节点，将UNION ALL改为UNION即可

兼容MySQL 5.x版本写法 #

如果使用的是不支持CTE的低版本MySQL，可以使用自定义变量实现：

SELECT 
    id, name, group_id
FROM (
    SELECT 
        t1.id, t1.name, t1.group_id,
        IF(FIND_IN_SET(group_id, @pids) > 0, @pids := CONCAT(@pids, ',', id), 0) AS is_child
    FROM 
        你的表名 t1,
        (SELECT @pids := '1,2') t2
    ORDER BY group_id, id
) t3 WHERE is_child != 0;

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