mybatis一对多查询内层排序问题

关注

原创最新推荐文章于 2026-04-10 17:22:47 发布 2.1w 阅读 AI 写同款· GEO 优化 ›

MyBatis一对多分页与PageHelper冲突：原理与解决方案分页查询是日常开发中高频使用的数据库交互方式，尤其在订单、商品等主从表结构场景中。当使用 MyBatis 这类ORM框架，配合 PageHelper 分页插件处理一对多关联查询时，往往会出现总数异常、当前页条数不符、数据重复等问题。其根源在于分页插件基于SQL物理行进行LIMIT截断，而一对多嵌套映射需要先对主表分页再填充子集合，两者语义错位。理解这一原理后，可以采用主表分页+集合回填、collection嵌套查询、SQL子查询先分页等方案，在保证数据准确的同时兼顾性能。本文结合典型订单列表案例，详细讲解问阅读详情

<!--根据板块id查询所有主题->指标->维度-->
    <resultMap id="TitleDimensionMap" type="com.etouch.admincenter.bean.ZhmdDiagnosisTitleBean">
        <id column="title_id" property="id"/>
        <result column="title_name" property="titleName"/>
        <collection property="diagnosisKpiBeanList" ofType="com.etouch.admincenter.bean.ZhmdDiagnosisKpiBean">
            <id column="kpi_id" property="id"/>
            <result column="kpi_name" property="kpiName"/>
            <result column="kpi_type" property="kpiType"/>
            <result column="calculate_method" property="calculateMethod"/>
            <result column="is_add_description" property="isAddDescription"/>
            <result column="description_remind" property="descriptionRemind"/>
            <result column="input_description" property="inputDescription"/>
            <result column="kpi_industry_standard" property="kpiIndustryStandard"/>
            <result column="kpi_brand_standard" property="kpiBrandStandard"/>
            <result column="kpi_industry_standard_diff" property="kpiIndustryStandardDiff"/>
            <result column="kpi_brand_standard_diff" property="kpiBrandStandardDiff"/>
            <result column="input_description" property="inputDescription"/>
            <result column="kpi_description" property="kpiDescription"/>
            <collection property="dimensionBeans" ofType="com.etouch.admincenter.bean.ZhmdDiagnosisKpiDimensionBean">
                <id column="kpi_dimension_id" property="id"/>
                <result column="diagnosis_id" property="diagnosisId"/>
                <result column="dimension_name" property="dimensionName"/>
                <result column="dimension_value" property="dimensionValue"/>
            </collection>
        </collection>
    </resultMap>

可设置多个排序规则,这样展示到前端页面时,解决每次打开页面显示顺序都不一样的问题
<select id="getDimensionOneStep" resultMap="TitleDimensionMap">
     select
    zdt.id as title_id,zdt.title_name,
    zdk.id as kpi_id,zdk.kpi_name,zdk.kpi_type,zdk.calculate_method,zdk.is_add_description,zdk.description_remind,zdk.kpi_description,
    zdkd.id as kpi_dimension_id, zdkd.dimension_name,zdd.dimension_value,
    zdkpd.input_description
    from zhmd_diagnosis_title zdt
    left join zhmd_diagnosis_kpi zdk on zdk.diagnosis_title_id = zdt.id
    left join zhmd_diagnosis_kpi_dimension zdkd on zdkd.diagnosis_kpi_id = zdk.id
    left join zhmd_dimension_diagnosis zdd on  zdd.dimension_id = zdkd.id and zdd.diagnosis_id = #{diagnosisId}
    left join zhmd_diagnosis_kpi_problem_desc zdkpd on zdkpd.kpi_id = zdk.id and zdkpd.diagnosis_id =#{diagnosisId}
    where zdt.diagnosis_model_id = #{modelId}
    order by zdt.create_date desc,zdk.create_date desc,zdkd.dimension_sort asc
</select>

postman查询结果