获取所有子部门
一、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