i2-bill-erp/sqlconfig/organ_school/base_organ_school_tree.tpl

SELECT * FROM (
    SELECT
        id,
        name,
        organ_id + 20000 parent
    FROM
        base_organ_school
WHERE
        del_flag = 0 and onoff != 99
UNION
    SELECT
        DISTINCT base_organ.id  + 20000 id,
    name,
        CASE WHEN base_organ.parent != 0 THEN base_organ.parent + 20000 END parent
    FROM
        base_organ
JOIN (
    SELECT
        organ_id,
        parent,
        base_organ.inheritance
    FROM
        base_organ_school, base_organ
    WHERE
        base_organ_school.del_flag = 0
) base_organ_school ON base_organ.id IN (base_organ_school.organ_id, base_organ_school.parent) OR base_organ_school.inheritance like concat('%|', base_organ.id, '|%')
WHERE
    del_flag = 0
)os_tree
ORDER BY parent,id