获取当前部门下的所有子部门

获取所有子部门

一、sqlite 写法

    <!--获取部门信息-->
    <select id="selectOrgList" parameterType="java.lang.String" resultMap="BaseResultMap">
        WITH RECURSIVE
          cte(ORG_CODE,ORG_NAME,ORG_PARENT_CODE) AS(
            SELECT ORG_CODE,ORG_NAME,ORG_PARENT_CODE FROM organization WHERE ORG_CODE=#{orgCode}
            UNION ALL
            SELECT a.ORG_CODE,a.ORG_NAME,a.ORG_PARENT_CODE FROM organization AS a INNER JOIN
                cte ON a.ORG_PARENT_CODE=cte.ORG_CODE
        )
        SELECT * FROM cte;
    </select>

二、mysql写法

SELECT * FROM (SELECT ID, DEPARTMENT_PARENT_CODE, DEPARTMENT_CODE, DEPARTMENT_NAME FROM organization WHERE DEPARTMENT_PARENT_CODE IS NOT NULL ORDER BY ID) rd,( SELECT @pid := '32rererererrererer' ) as pd
WHERE
	FIND_IN_SET( DEPARTMENT_PARENT_CODE, @pid ) 
	AND @pid := concat(@pid, ',', DEPARTMENT_CODE )
UNION
SELECT ID,DEPARTMENT_PARENT_CODE,DEPARTMENT_CODE,DEPARTMENT_NAME,( SELECT @pid := '32rererererrererer' ) AS pd FROM organization WHERE DEPARTMENT_CODE='32rererererrererer';

//使用递归sql的时候务必要用ORDER BY 进行排序否则会丢失子节点

备注：数据库相关字段查看https://blog.csdn.net/qq_39381529/article/details/105846136