抓组织层级树结构

抓当前组织及下级组织：
WITH RECURSIVE AA AS (
 SELECT HIGHER_ORGANIZATION AS ORGANIZATION FROM BAS_ORGANIZATION_HIERARCHY
    WHERE HIGHER_ORGANIZATION = '330101'
    AND ( VALID_DATE <= NOW() AND (INVALID_DATE >= NOW() OR INVALID_DATE IS NULL))
 UNION
 SELECT BB.ORGANIZATION FROM BAS_ORGANIZATION_HIERARCHY BB, AA
  WHERE AA.ORGANIZATION = BB.HIGHER_ORGANIZATION
    AND ( VALID_DATE <= NOW() AND (INVALID_DATE >= NOW() OR INVALID_DATE IS NULL))
 UNION
 SELECT CC.ORGANIZATION FROM BAS_ORGANIZATION_HIERARCHY CC
  WHERE ORGANIZATION = '330101' AND (VALID_DATE <= NOW() AND (INVALID_DATE >= NOW() OR INVALID_DATE IS NULL))
) SELECT ORGANIZATION FROM AA;

抓当前组织的所有上级组织：
WITH RECURSIVE AA AS (
 SELECT ORGANIZATION AS HIGHER_ORGANIZATION FROM BAS_ORGANIZATION_HIERARCHY
    WHERE ORGANIZATION = '330105'
    AND ( VALID_DATE <= NOW() AND (INVALID_DATE >= NOW() OR INVALID_DATE IS NULL))
 UNION
 SELECT BB.HIGHER_ORGANIZATION FROM BAS_ORGANIZATION_HIERARCHY BB, AA
  WHERE AA.HIGHER_ORGANIZATION = BB.ORGANIZATION
    AND ( VALID_DATE <= NOW() AND (INVALID_DATE >= NOW() OR INVALID_DATE IS NULL))
) SELECT HIGHER_ORGANIZATION FROM AA WHERE AA.HIGHER_ORGANIZATION <> '330105';

转载于:https://www.cnblogs.com/sikuaiwu/p/11344867.html