sql递归查出某个值下的所有子集数据

一、mapper层

List<Long> selectListById(@Param "grpId") String grpId);

二、xml层

<select id="selectListById" resultType="java.lang.Long">
      WITH RECURSIVE subordinates AS (
          SELECT id,str01,tenant_id
          FROM cloud_base_person
          WHERE str01 = #{grpId}
          UNION ALL
          SELECT t.id, t.str01, t.tenant_id
          FROM cloud_base_persont
          INNER JOIN subordinates s ON t.str01 = s.id
          WHERE t.deleted != '1' AND t.tenant_id = s.tenant_id
      )
      SELECT id FROM subordinates;
</select>

三、实践

查某一部门下的全部user信息

<select id="getUserPageByDeptId" resultType="cn.iocoder.yudao.vo.APPUserInfoPage">
        WITH RECURSIVE dept_cte AS (
            SELECT
            id,
            name,
            parent_id
            FROM
            system_dept
            WHERE
            id = #{deptId} AND deleted != '1'

            UNION ALL

            SELECT
            d.id,
            d.name,
            d.parent_id
            FROM
            system_dept d
            INNER JOIN
            dept_cte cte ON d.parent_id = cte.id
            WHERE
            d.deleted != '1'
        )
        
        SELECT
        u.id,
        u.nickname,
        d.id AS deptId,
        d.name AS deptName
        FROM
        system_users u
        LEFT JOIN
        dept_cte d ON u.dept_id = d.id
        WHERE
        u.deleted != '1'

        <if test="nickName != null and nickName != ''">
            AND u.nickname LIKE CONCAT('%', #{nickName}, '%')
        </if>
        ORDER BY
        u.nickname ASC;
    </select>