mybatis-plus实现多表联查 分类汇总

参考：mybatis-plus实现多表联查
MyBatis @Select注解介绍：基本用法与动态SQL拼写
SpringBoot系列（二十七）MyBatisPlus联表查询

第一步，定义Bean对象

extends继承LEFT JOIN的主表，然后把需要汇总的数据在Bean里定义出来。后面用来获取数据用

package com.tj.caiwu.domain;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.tj.base.domain.BaseBaobeiM;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
/**
 * 财务-按归属和转账人进行汇总
 */
@Data
public class CaiwuBaobeiDayMoney extends BaseBaobeiM {
    //日期
    private Date date;
    //付款人
    private String payer;
    //归属
    private String shopbelong;
    //合计
    private Double total;
    //付款金额
    private Integer goodPriceSum;
    //佣金
    private Integer commissionSum;
    //人工费
    private Integer rengongfeiSum;
}

第二步，在Mapper里定义join和groupby的方法

可以使用@Select注解直接编写sql语句

package com.tj.caiwu.mapper;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.tj.caiwu.domain.CaiwuBaobeiDayMoney;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
 * @Entity com.tj.caiwu.domain.CaiwuBaobeiDayMoney
 */
public interface CaiwuBaibeiDayMoneyMapper extends BaseMapper<CaiwuBaobeiDayMoney> {
    @Select("SELECT b.payer," +
            "b.shopbelong," +
            "SUM(a.good_price) AS goodPriceSum ," +
            "SUM(b.commission) AS commissionSum," +
            "SUM(b.rengongfei) AS rengongfeiSum " +
            "from base_baobei_m AS a LEFT JOIN base_baobei AS b " +
            "on a.baobei_id=b.id " +
            "${ew.customSqlSegment}")
    List<CaiwuBaobeiDayMoney> tjList(@Param(Constants.WRAPPER) Wrapper wrapper);
}

${ew.customSqlSegment}是包含了WHERE ，${ew.sqlSegment}是不包含where的，要注意一下

第三步，调用Mapper里定义的，添加LambdaQueryWrapper条件

@Resource
private CaiwuBaibeiDayMoneyMapper baibeiDayMoneyMapper;
@Test
public void test() {
    //设置查询条件，按什么进行分类汇总
    LambdaQueryWrapper<CaiwuBaobeiDayMoney> lqw = new LambdaQueryWrapper<>();
    lqw.groupBy(CaiwuBaobeiDayMoney::getShopbelong);
    List<CaiwuBaobeiDayMoney> allBaseBaobeiInfo = baibeiDayMoneyMapper.tjList(lqw);
    log.info("结果：{}", allBaseBaobeiInfo);
}

附：自定义分页查询的方法

参考：mybatis-plus多表联合分页查询最佳实践

    @Select("SELECT b.payer," +
            "b.shopbelong," +
            "SUM(a.good_price*a.number) AS goodPriceSum , " +
            "SUM(b.commission*a.number) AS commissionSum, " +
            "SUM(b.rengongfei*a.number) AS rengongfeiSum, " +
            "SUM(a.number) AS numberSum " +
            "from base_baobei_m AS a LEFT JOIN base_baobei AS b " +
            "on a.baobei_id=b.id " +
            "${ew.customSqlSegment}")
    Page<CaiwuBaobeiDayMoney> tjPage(Page<CaiwuBaobeiDayMoney> page, @Param(Constants.WRAPPER) Wrapper wrapper);