mysql中的递归函数recursive

递归部门

 WITH recursive dept_tree AS (SELECTsd.mine_id AS mine_id,sd.dept_id AS dept_id,sd.tenant_id AS tenant_id,sd.order_num,sd.dept_name AS topName,sd.dept_id AS topIdFROMsys_dept sdWHERE<!-- 加上or后也会查询出dept节点  sd.parent_id = #{deptId} or sd.dept_id = #{deptId}  UNION ALL-->sd.parent_id = #{deptId} UNION ALLSELECTsd1.mine_id AS mine_id,sd1.dept_id AS dept_id,sd1.tenant_id AS tenant_id,pt2.order_num,pt2.topName AS topName,pt2.topId AS topIdFROMsys_dept sd1JOIN dept_tree pt2 ON sd1.parent_id = pt2.dept_id) SELECTtopId,topName,tenant_id,order_num,mine_idFROMdept_treeORDER BY  order_num

查出来每个部门的煤矿封装到实体返回。

    <resultMap id="mineIdsMap" type="org.dromara.common.tools.base.MineIdsBaseVo"><result column="topId" property="topId"/><result column="topName" property="topName"/><result column="tenant_id" property="tenantId"/><result column="order_num" property="orderNum"/><collection property="mineIds" ofType="string"><result column="mine_id"/></collection></resultMap>