<resultMap type="CourseType" id="courseTypeMap">
<id property="id" column="p1Id" />
<result property="typeName" column="p1Name" />
<result property="parentId" column="p1ParentId" />
<result property="status" column="p1Status" />
<collection property="children" javaType="CourseType" column="p1ParentId" >
<id property="id" column="p2Id" />
<result property="typeName" column="p2Name" />
<result property="parentId" column="p2ParentId" />
<result property="status" column="p2Status" />
<collection property="children" javaType="CourseType" column="p2ParentId" >
<id property="id" column="p3Id" />
<result property="typeName" column="p3Name" />
<result property="parentId" column="p3ParentId" />
<result property="status" column="p3Status" />
</collection>
</collection>
</resultMap>
<select id="selectAllCourseType" resultMap="courseTypeMap">
select
p1.id p1Id,p1.type_name p1Name,p1.status p1Status,p1.parent_id p1ParentId,
p2.id p2Id,p2.type_name p2Name,p2.status p2Status,p2.parent_id p2ParentId,
p3.id p3Id,p3.type_name p3Name,p3.status p3Status,p3.parent_id p3ParentId
from t_course_type p1
left join t_course_type p2 on p2.parent_id = p1.id
left join t_course_type p3 on p3.parent_id = p2.id
where p1.parent_id is null
</select>