mysql 数据表父子关联数据查询sql

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_id
SELECT organization_id FROM (
SELECT t1.organization_id,
if (FIND_IN_SET(parent_organization_id, @pids) > 0, @pids := CONCAT(@pids, ',', organization_id), 0) AS ischild
FROM 
(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_id
SELECT t2.organization_id from (
SELECT @r as _id,
(SELECT @r := parent_organization_id FROM person_organization
WHERE 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 = t2.organization_id order by t1.lvl asc 
)
</select>