left join 导致的分页错误_left jion 导致分页不对

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

t_rule表数据

t_label表数据

t_rule_label表数据

使用mybatis-plus 查询

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

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')
1
2
3
4
5

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_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>
		<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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

行转列解决

对于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;
}
1
2
3
4
5
6
7

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

<select id="getRulesPage" resultMap="string">
	select distinct rule.id
    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>
		<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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

第二次查询，将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_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>
		<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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23