sql 递归树

WITH RECURSIVE org_hierarchy AS (
-- 锚点：带出公司名，只在这层过滤
SELECT h.*, c.cn_full_name_ company_name_
FROM hr_org_info h
LEFT JOIN hr_org_company c ON c.id_ = h.company_id_
WHERE c.cn_full_name_ = 'aa'

UNION

-- 递归：直接基于原表往下找，不用再 join 公司
SELECT h.*, NULL company_name_
FROM hr_org_info h
JOIN org_hierarchy c ON h.id_ = c.parent_id_
)
SELECT * FROM org_hierarchy;