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由三个主要部分组成:
UNION DISTINCT
运算符与锚成员相连。递归CTE的执行顺序如下:
R0
),并使用该基本结果集进行下一次迭代。Ri
结果集作为输入执行递归成员,并将Ri+1
作为输出。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