left join 导致的分页错误

本文主要是介绍left join 导致的分页错误，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

行转列解决
先分页再组装

背景：有规则表t_rule,标签表t_label,中间表t_rule_label,根据t_rule的name和t_label表的name组合查询规则

t_rule表数据

id	name
1	rule1
2	rule2

t_label表数据

id	name
1	label1
2	label2

t_rule_label表数据

rule_id	label_id
1	1
1	2
2	1
2	2

使用mybatis-plus 查询

Page<CheckRule> page = new Page<>(query.getPageNum(), query.getPageSize());

select rule.id,rule.name,label.id label_id,label.name label_name
from t_rule rule
left join t_rule_label con on con.rule_id = rule.id
left join t_label label on label.id = con.label_id
where rule.name like '%ru%' and label.id in ('1','2')

left join 导致查询出4条数据
在这里插入图片描述

page.getTotal() 和 page.getRecords() 对应不上
因为一对多导致getTotal()数量比getRecords()多，getRecords()为resultMap 组合之后的数据

<resultMap id="ruleResultMap" type="com.yss.rule.entity.Rule"><id column="id" property="id" /><result column="name" property="name" /><collection property="labelList" ofType="com.yss.rule.entity.Label" ><id column="lable_id" property="id" /><result column="lable_name" property="name" /></collection></resultMap><select id="getRules" resultMap="rulListMap">select rule.id,rule.name,label.id label_id,label.name label_namefrom t_rule ruleleft join t_rule_label con on con.rule_id = rule.idleft join t_label label on label.id = con.label_id<where><if test="ruleAndLabelVo.ruleName !=null and ruleAndLabelVo.ruleName !=''">and rule.name like '%${ruleAndLabelVo.ruleName }%'</if><if test="ruleAndLabelVo.labelIds !=null and ruleAndLabelVo.labelIds.size() !=0">and lable.id in<foreach collection="ruleAndLabelVo.labelIds" index="index" item="item" open="(" separator="," close=")">#{item}</foreach></if></where>                     
</select>

行转列解决

对于oracle 行转列

wmsys.wm_concat，oracle 10g推出的函数，12c以后被去掉 select
id,wmsys.wm.concat(name) lable_name from t_label group by id

LISTAGG,是oracle11g推出的函数 select id, listagg(lable_name,’,’) within
group (order by lable_name) lable_name from t_label group by id;

对于mysql 行转列

SELECT GROUP_CONCAT(cast(user_id as char(10)) SEPARATOR ‘,’) as id
from user;

先分页再组装

public class RuleAndLabelVo{//前端传递条件private String ruleName;private List<String> labelIds;//第一次查询出的t_rule的主键集合private List<String> ruleIds;
}

第一次查询，分页查询,查询出t_rule的主键集合

<select id="getRulesPage" resultMap="string">select distinct rule.idfrom t_rule ruleleft join t_rule_label con on con.rule_id = rule.idleft join t_label label on label.id = con.label_id<where><if test="ruleAndLabelVo.ruleName !=null and ruleAndLabelVo.ruleName !=''">and rule.name like '%${ruleAndLabelVo.ruleName }%'</if><if test="ruleAndLabelVo.labelIds !=null and ruleAndLabelVo.labelIds.size() !=0">and lable.id in<foreach collection="ruleAndLabelVo.labelIds" index="index" item="item" open="(" separator="," close=")">#{item}</foreach></if></where>  order by rule.id
</select>

第二次查询，将t_rule的主键集合带入查询

List ruleIds = checkRuleMapper.getRulesPage(page,
ruleByRuleOrLabelVo); ruleAndLabelVo.setRuleIds(ruleIds);

<resultMap id="ruleResultMap" type="com.yss.rule.entity.Rule"><id column="id" property="id" /><result column="name" property="name" /><collection property="labelList" ofType="com.yss.rule.entity.Label" ><id column="lable_id" property="id" /><result column="lable_name" property="name" /></collection></resultMap><select id="getRules" resultMap="rulListMap">select rule.id,rule.name,label.id label_id,label.name label_namefrom t_rule ruleleft join t_rule_label con on con.rule_id = rule.idleft join t_label label on label.id = con.label_id<where><if test="ruleAndLabelVo.ruleIds !=null and ruleAndLabelVo.ruleIds.size() !=0">and rule.id in<foreach collection="ruleAndLabelVo.ruleIds" index="index" item="item" open="(" separator="," close=")">#{item}</foreach></if></where>                     
</select>