--1、不是末级节点的记录设置状态为【关闭状态】
select
a.id,a.parent_id,a.project_code,a.project_name,a.tree_level,a.sort,a.remark,a.creator_id,a.creator,a.create_date,a.creator_dept_id,a.creator_dept,a.company_id,a.company_name,a.bu_id,a.bu_name,
case (select count(1) from table_name b where b.parent_id = a.id)
when 0 then 'open'
else 'closed' end tree_state
from table_name a
where 1=1
<if test="expand != null and expand == 'level'">
<if test="treeLevels != null">
and a.tree_level between 0 and #{treeLevels}
</if>
</if>
order by a.sort, a.project_code
/** 关键sql
case (select count(1) from table_name b where b.parent_id = a.id)
when 0 then 'open'
else 'closed' end tree_state
*/
--2、查找满足条件的末级节点及其所有父级节点
select distinct * from table_name l
connect by prior l.parent_id = l.id
start with 1 = 1
<if test="productionCostId != null and productionCostId != '' ">
and l.production_cost_id=#{productionCostId}
and l.total_price=0
and not exists(select 1 from table_name c where c.production_cost_id=#{productionCostId}
and l.id=c.parent_id )
</if>
<if test="exendsSql != null and exendsSql != '' ">
${exendsSql}
</if>
ORDER BY l.TREE_LEVEL,l.SORT
/** 关键sql
connect by prior l.parent_id = l.id
start with 1 = 1
and not exists(select 1 from table_name c where l.id=c.parent_id )
*/