select t.id, t.name_v,t.pid, level from t_resource_type t start with t.pid= '-1' connect by prior t.id = t.pid 解析: select 列 from 表名 start with 根节点限定条件 connect by prior 连接条件(注1) where 总体过滤条件 注1: level 表示层号 从1开始 可根据level进行排序 prior 表示前一条记录 放在哪列前哪列就是前一条记录 如此sql可改为 select t.id, t.name_v,t.pid from t_resource_type t start with t.pid= '-1' connect by t.id = prior t.pid
实际应用中的例子:
SELECT a.id,a.name,a.parent_id parentId,a.code,a.type,a.grade,a.del_flag delFlag FROM sys_office a
<where> <if test="parentId != null"> a.grade>0 connect by prior a.id = a.parent_id start with a.id = #{parentId} order by a.grade </if> </where>