MySQL递归查询WITH RECURSIVE

WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;//原文出自【易百教程】，商业转载请联系作者获得授权，非商业请保留原文链接：https://www.yiibai.com/mysql/recursive-cte.html

递归CTE由三个主要部分组成：

• 形成CTE结构的基本结果集的初始查询(initial_query)，初始查询部分被称为锚成员。
• 递归查询部分是引用CTE名称的查询，因此称为递归成员。递归成员由一个UNION ALL或UNION DISTINCT运算符与锚成员相连。
• 终止条件是当递归成员没有返回任何行时，确保递归停止。

递归CTE的执行顺序如下：

1. 首先，将成员分为两个：锚点和递归成员。
2. 接下来，执行锚成员形成基本结果集(R0)，并使用该基本结果集进行下一次迭代。
3. 然后，将Ri结果集作为输入执行递归成员，并将Ri+1作为输出。
4. 之后，重复第三步，直到递归成员返回一个空结果集，换句话说，满足终止条件。
5. 最后，使用UNION ALL运算符将结果集从R0到Rn组合

WITH recursive t1 ( org_id, prod_id, agent_id, up_agent_id, agent_level, order_by_num ) AS (
                SELECT
                    org_id,
                    prod_id,
                    agent_id,
                    up_agent_id,
                    agent_level,
                    1 AS order_by_num
                FROM
                    agent_level
                WHERE
                    1 = 1
                    AND agent_id = #{agentId,jdbcType=VARCHAR}
                    AND prod_id  = #{prodId,jdbcType=VARCHAR}
                UNION ALL
                SELECT
                    t2.org_id,
                    t2.prod_id,
                    t2.agent_id,
                    t2.up_agent_id,
                    t2.agent_level,
                    order_by_num + 1 AS order_by_num
                FROM
                    t1,
                    agent_level AS t2
                WHERE
                    1 = 1
                    AND t1.up_agent_id = t2.agent_id
                    AND t1.prod_id     = t2.prod_id
                )
            SELECT
                t1.org_id,
                t1.prod_id,
                t1.agent_id,
                t1.up_agent_id,
                t1.agent_level,
                t1.order_by_num
            FROM
                t1
        ORDER BY  t1.order_by_num asc