基於mybatis一對多查詢內層排序的問題
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
這種寫法是根據最左邊的字段進行第一排序,如果第一排序字段有並列的,則根據第二字段再去排一次,依次類推。
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。
推薦閱讀:
- 關於Mybatis使用collection分頁問題
- MyBatis實現兩種查詢樹形數據的方法詳解(嵌套結果集和遞歸查詢)
- 關於mybatis一對一查詢一對多查詢遇到的問題
- mybatis如何使用註解實現一對多關聯查詢
- springboot使用mybatis一對多的關聯查詢問題記錄