mybatis一对多查询内层排序
<!--根据板块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查询结果
mybatis多排序问题
在很多业务场景中，会有多排序问题，mybatis支持多排序，写法如下：
ORDER BY contract.contractCount DESC, reward.rewardTotal DESC, new_job.newJobCount DESC这种写法是根据最左边的字段进行第一排序，如果第一排序字段有并列的，则根据第二字段再去排一次，依次类推。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程网。
