sql递归

递归向下查询是用 虚拟表的id(e3)去联结 递归表的parent_id(ccc1)
递归向上查询是用 虚拟表的parent_id(e3)去联结 递归表的id(ccc1)
WITH RECURSIVE le (catalog_id,
catalog_name,
catalog_type,
parent_id,
hos_id,
catalog_doc_code,
catalog_dept_code,
catalog_state) as
(
select ccc.id as catalog_id,
ccc.name as catalog_name,
ccc.catalog_type as catalog_type,
ccc.parent_id as parent_id,
ccc.hos_id as hos_id,
ccc.doc_code as catalog_doc_code,
ccc.dept_code as catalog_dept_code,
ccc.state as catalog_state
from his.cli_combine_catalog ccc
where ccc.hos_id = #{hosId}
and ccc.state = ‘1’
and ccc.id = #{id}
union all

select ccc1.id as catalog_id,
ccc1.name as catalog_name,
ccc1.catalog_type as catalog_type,
ccc1.parent_id as parent_id,
ccc1.hos_id as hos_id,
ccc1.doc_code as catalog_doc_code,
ccc1.dept_code as catalog_dept_code,
ccc1.state as catalog_state
from his.cli_combine_catalog ccc1,
le e3
where e3.catalog_id = ccc1.parent_id and ccc1.state=‘1’
)
select *
from le;