现有数据库表：
CREATE TABLE `dept_p` (
`DEPT_ID` varchar(40) NOT NULL,
`DEPT_NAME` varchar(50) DEFAULT NULL,
`PARENT_ID` varchar(40) DEFAULT NULL COMMENT '自关联，多对一',
`STATE` int(11) DEFAULT NULL COMMENT '1启用0停用',
`CREATE_BY` varchar(40) DEFAULT NULL COMMENT '登录人编号',
`CREATE_DEPT` varchar(40) DEFAULT NULL COMMENT '登录人所属部门编号',
`CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`UPDATE_BY` varchar(40) DEFAULT NULL,
`UPDATE_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`DEPT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mybatis的xml文件：
<!-- 需要显示部门的全部信息，并且需要显示上级部门的ID和上级部门的名称 -->
<select id="findDeptList" resultMap="deptRM">
SELECT * FROM
(SELECT * FROM dept_p)d
LEFT JOIN
(SELECT dept_id AS p_id,dept_name AS p_name FROM dept_p)p
ON d.parent_id=p.p_id
ORDER BY dept_id
</select> <!--封装结果集 -->
<resultMap type="Dept" id="deptRM" autoMapping="true">
<id property="deptId" column="DEPT_ID"/>
<id property="deptName" column="DEPT_NAME"/>
<id property="state" column="STATE"/>
<id property="createTime" column="CREATE_TIME"/>
<id property="updateTime" column="UPDATE_TIME"/>
<!--封装上级部门 一对一 -->
<association property="parentDept" javaType="Dept">
<id property="deptId" column="P_ID"/>
<result property="deptName" column="P_NAME"/>
</association>
</resultMap>