向下查第几层

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>