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_idas 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>
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！