mysql中的递归函数recursive

递归部门

 WITH recursive dept_tree AS (
        SELECT
        sd.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 topId
        FROM
        sys_dept sd
        WHERE
        <!-- 加上or后也会查询出dept节点  sd.parent_id = #{deptId} or sd.dept_id = #{deptId}  UNION ALL-->
        sd.parent_id = #{deptId} UNION ALL
        SELECT
        sd1.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 topId
        FROM
        sys_dept sd1
        JOIN dept_tree pt2 ON sd1.parent_id = pt2.dept_id
        ) SELECT
        topId,
        topName,
        tenant_id,
        order_num,
        mine_id
        FROM
        dept_tree
        ORDER 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>