start with connect by level 实现树

具体的sql为
select level treelevel,
eva.state,
t.id,
case
when t.parentid is null then
''
else
t.parentid || ''
end parentid,
t.summary,
t.score,
t.content,
t.standardscore,
t.selfAssessmentScore,
t.remark,
t.scoreReason,
t.ruleType,
case
when (select count(1) from [color=red]abc[/color] dt where dt.parentid = t.id) = 0 then
'1'
else
'0'
end leaf,
case
when level = 3 then
'0'
else
'1'
end expanded
from [color=red]abc[/color] t, [color=red]def[/color] eva
where t.defid = eva.id
and t.defid = #value#
start with t.id in (select id
from [color=red]abc[/color] d
where d.parentid is null
and d.defid = #value#)
connect by t.parentid = prior t.id ORDER SIBLINGS BY t.seq
****************************************************************
这段sql实现的是一个树形表，使用start with connect by prior level来实现，这个树有两张表：abc和def，abc是def的子表(abc.defid = def.id)。def存的是这个树叫什么名字，是哪个单位，哪个月份的树，abc存的是这个树具体的细则。
leaf和expanded映射成javaBean后是两个boolean类型的值，用来判断图标。
因此，排除def的干扰后，由abc的递归查询来实现这颗树：
[color=gray] start with t.id in (select id
from abc d
where d.parentid is null
and d.defid = #value#)
connect by t.parentid = prior t.id ORDER SIBLINGS BY t.seq[/color]

首先确定递归查询的范围：[color=gray]select id
from abc d
where d.parentid is null
and d.defid = #value#
defid=#value#[/color]这棵树的所有顶层节点(因为这颗树只有两层)

递归查询的下次的parentid是上次的id，根据seq字段进行兄弟姐妹排序(ORDER SIBLINGS BY)

由connect by生成的系统默认字段level来生成expanded字段的值 :arrow: