几种常用递归查询

向下查第几层
select max(LEVEL) as maxLevel
from T_lab a
start with a.lab_id ='2015050510303643710'
connect by prior a.lab_parentid = a.lab_id

<!-- 递归向下查询所有机构 -->
<select id="deptDataInit" parameterClass="map" resultClass="dto">
select deptname as text, deptid as id, parentid, type from EADEPT a where 1=1
<isNotEmpty prepend="AND" property="filtertype">
type != #filtertype#
</isNotEmpty>
start with a.deptid=#deptid# connect by prior a.deptid=a.parentid
order by type asc, sortno asc
</select>

<!-- 递归向上查询所有机构 -->
<select id="getParents" parameterClass="map" resultClass="dto">
select deptname as name, deptid as id, parentid, type from EADEPT a where 1=1
<isNotEmpty prepend="AND" property="filtertype">
type != #filtertype#
</isNotEmpty>
start with a.deptid=#deptid# connect by prior a.parentid=a.deptid
order by type asc, sortno asc
</select>

----------------------------------------------------------------------------------------------------------------

<sqlMap namespace="lab" >
<typeAlias alias="dto" type="org.eredlab.g4.ccl.datastructure.impl.BaseDto" />
<resultMap class="Dto" id="labParent">
<result property="id" column="id"/>
<result property="lab_userid" column="lab_userid"/>
<result property="lab_username" column="lab_username"/>
<result property="parentid" column="parentid"/>
<result property="name" column="name"/>
<result property="s_labfloor" column="s_labfloor"/>
<result property="s_labaddr" column="s_labaddr"/>
<result property="s_creater" column="s_creater"/>
<result property="d_createtime" column="d_createtime"/>
<result property="s_mark1" column="s_mark1"/>
<result property="children" column="id" select="lab.querylabchildren" javaType="java.util.List"/>
</resultMap>
<!-- 查询实验室 -->
<select id="querylabList" parameterClass="map" resultMap="labParent" remapResults="true">
select t.lab_id as id,t.lab_userid,t.lab_username,t.s_labname as name,t.lab_parentid as parentid,t.s_labfloor,t.s_labaddr,t.s_creater,
TO_CHAR(t.d_createtime,'yyyy-mm-dd') as d_createtime,t.s_mark1
from T_lab t where 1=1
<dynamic>
<isNotNull prepend="and" property="lab_parentid">
t.lab_parentid=#lab_parentid#
</isNotNull>
<isNotNull prepend="and" property="lab_id">
t.lab_id=#lab_id#
</isNotNull>
</dynamic>
order by t.lab_id
</select>
<!-- 查询下级实验室 -->
<select id="querylabchildren" parameterClass="String" resultMap="labParent" remapResults="true">
select t.lab_id as id,t.lab_userid,t.lab_username,t.s_labname as name,t.lab_parentid as parentid,t.s_labfloor,t.s_labaddr,t.s_creater,
TO_CHAR(t.d_createtime,'yyyy-mm-dd') as d_createtime,t.s_mark1
from T_lab t where 1=1 and t.lab_parentid=#id#
</select>

转载于:https://www.cnblogs.com/wei-java/p/4540076.html