mysql数据库表person_organization中,主键是organization_id,另有一列parent_organization_id指向父机构的数据记录;通过以下sql可以取出指定organization_id所在的树状结构枝干上所有的organization_id:
<select id="queryBranchIds" parameterType="java.lang.String" resultType="java.lang.String">(-- 查找所有子孙organization_idSELECT organization_id FROM (SELECT anization_id,if (FIND_IN_SET(parent_organization_id, @pids) > 0, @pids := CONCAT(@pids, ',', organization_id), 0) AS ischildFROM (SELECT organization_id, parent_organization_id FROM person_organization t WHERE t.status = '0' ORDER BY parent_organization_id, organization_id, type) t1,(SELECT @pids := #{organizationId}) t2) t3 WHERE ischild != '0')UINION(-- 查找所有父祖organization_idSELECT anization_id from (SELECT @r as _id,(SELECT @r := parent_organization_id FROM person_organizationWHERE organization_id = _id) as parent_organization_id, @l := @l + 1 as lvl FROM(SELECT @r := #{organizationId}, @l := '') vars, person_organization h WHERE @r <![CDATA[ <> ]]> '' and h.status = '0') t1 join person_organization t2 on t1._id = anization_id order by t1.lvl asc )</select>