mybatis分步查询

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace命名空间mapper方式下，必须为mapper接口的长类名-->
<mapper namespace="com.wance.mapper.FamilyMapper">
    <!--自定义映射结构。让不同的数据库字段名和java类属性进行匹配-->
    <resultMap id="FamilyResultMap" type="Family">
        <id property="fid" column="fid"/>
        <result property="fname" column="fname"/>
        <!--使用collection标签，映射n端的集合属性persons.column外键名，ofType是集合的元素类型-->
        <collection property="humans" column="fid" select="selectHumansByfid" fetchType="lazy"/>
    </resultMap>

    <!--自定义的sql段。查询一个动物-->
    <select id="findWithMaxPetCount"  resultMap="FamilyResultMap">
          select * from family where fid in
                (
                select t2.fid from
                (
                  select  family.fid,count(1) as 动物数 from  family,human,pet  where human.fid= family.fid and pet.hid= human.hid
                group by family.fid order by 动物数
                ) t2
                where 动物数 =
                (
                    select max(动物数) from
                    (select  count(1) as 动物数 from  family,human,pet  where human.fid= family.fid and pet.hid= human.hid
                    group by family.fid
                  ) t
                )
              )
    </select>
    <!--如果需要关联到human对象的pet信息，则要引用别的mapper配置的map
    <select id="selectHumansByfid" parameterType="int" resultMap="com.wance.mapper.HumanMapper.HumanResultMap">
    -->
    <select id="selectHumansByfid" parameterType="int" resultType="Human">
        select * from human where fid = #{fid}
    </select>
</mapper>