mybatis mysql 自定义sql，实际统计sql/子查询,union两张表实现提取公共字段并分页、筛选、排序

文章目录

一.详解
- 1.业务需求：
- 2.解决思路：
二.实例

一.详解

1.业务需求：

业务为个人重大事项报告和单位重大事项报告两种。业务类似于：发起办公流程-》部门领导审核-》报备给单位领导。流程发起时，两种流程时分开发起的，所以设计时设计了两张表来记录两种业务。流程发起、审核、报备，设计的是完全独立的。后来由于甲方的脑残需求，要求在单位领导查看报备报备信息时，两张业务合并在一个列表里，并且要按时间排序，要有分页。

2.解决思路：

自定义sql，利用union 将两张业务表的公共字段合并成一个虚拟表

		(
		SELECT
			gaform_lpt_30.file_id file_id,
			gaform_lpt_30.unit unit,
			gaform_lpt_30.`name` `name`,
			gaform_lpt_30.gettime time 
		FROM
			`gaform_lpt_30` 
		WHERE
			allagree = 1 
		) UNION ALL
		(
		SELECT
			gaform_lpt_29.file_id file_id,
			gaform_lpt_29.report_unit unit,
			gaform_lpt_29.`report_name` `name`,
			gaform_lpt_29.gettime time 
		FROM
			`gaform_lpt_29` 
		WHERE
			allagree = 1 
		)

然后在上面union结果的外层套上排序
这里需要注意一下: 合并 union两边的数据列数(as的字段列名也要)必须一致,否则会合并失败

SELECT
	* 
FROM
	(
		(
		SELECT
			gaform_lpt_30.file_id file_id,
			gaform_lpt_30.unit unit,
			gaform_lpt_30.`name` `name`,
			gaform_lpt_30.gettime time 
		FROM
			`gaform_lpt_30` 
		WHERE
			allagree = 1 
		) UNION ALL
		(
		SELECT
			gaform_lpt_29.file_id file_id,
			gaform_lpt_29.report_unit unit,
			gaform_lpt_29.`report_name` `name`,
			gaform_lpt_29.gettime time 
		FROM
			`gaform_lpt_29` 
		WHERE
			allagree = 1 
		) 
	) AS t #此处一定注意，union后务必派生声明虚拟表，不然会出现报错
ORDER BY
	time DESC #time是派生的虚拟表的字段

这时排序和两表合并就完成了。分页利用mybatis框架，只要给dao层方法正常传IPage就可以了(本来怎么分页,这里就可以直接接分页对象了)

以下是java代码：
controller层


    @ApiOperation("报备列表-29+30共用")
    @PostMapping("selectBblist")
    public ServiceResult selectBblist(@RequestParam Integer page,@RequestParam Integer pageCount) {
        IPage<Lpt2930BaobeiVO> page2=  gaformLpt29Service.selectBb(page,pageCount);
       return success(page2);
    }

service层

  @Override
    public IPage<Lpt2930BaobeiVO> selectBb(Integer page, Integer pageCount) {

        return gaformLpt29Dao.selectBb( new Page<>(page, pageCount));
    }

dao层
(只是把上边的sql拿到了这里)

  @Select("SELECT\n" +
            "\t*\n" +
            "FROM\n" +
            "\t(\n" +
            "\t\t( SELECT  gaform_lpt_30.file_id file_id, gaform_lpt_30.unit unit, gaform_lpt_30.`name` `name`, gaform_lpt_30.gettime time ,gaform_lpt_30.event_id event_id FROM `gaform_lpt_30` WHERE allagree = 1 ) \n" +
            "\t\tUNION ALL\n" +
            "\t\t( SELECT gaform_lpt_29.file_id file_id, gaform_lpt_29.report_unit unit, gaform_lpt_29.`report_name` `name`, gaform_lpt_29.gettime time,gaform_lpt_29.event_id event_id FROM `gaform_lpt_29` WHERE allagree = 1 ) \n" +
            "\n" +
            ")  as t ORDER BY time DESC")
    IPage<Lpt2930BaobeiVO> selectBb(IPage page);//此处传入page 插件会自动实现分页

接收数据的vo

@Data
public class Lpt2930BaobeiVO {
    public String fileId;
    public String unit;
    public String name;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    public Date time;
    public String eventId;

}

原文链接:https://blog.youkuaiyun.com/qq_27037397/article/details/121329562

二.实例

这里我把整个类贴过来了,很长,不过很贴近实际

serviceImpl 类

package com.boot.reservation.impl.statistics;

import cn.hutool.core.bean.BeanUtil;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.ClientManagementEntity;
import com.boot.reservation.entity.domain.vo.StatisticsGranaryWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseDetailsWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseWebsiteVO;
import com.boot.reservation.entity.domain.vo.StorageInfoVO;
import com.boot.reservation.mapper.ClientManagementMapper;
import com.boot.reservation.mapper.PfuMapper;
import com.boot.reservation.mapper.StatisticsAppletMapper;
import com.boot.reservation.mapper.StatisticsWebsiteMapper;
import com.boot.reservation.service.project.ProjectManagerService;
import com.boot.reservation.service.statistics.StatisticsWebsiteService;
import com.iciyun.project.domain.security.PerFrontUserEntity;
import com.iciyun.ticket.user.authentication.UserSessionRedisUtil;
import com.iciyun.ticket.util.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.LinkedList;
import java.util.List;
import java.util.stream.Collectors;

@Service
public class StatisticsWebsiteServiceImpl implements StatisticsWebsiteService {

    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private StatisticsWebsiteMapper statisticsWebsiteMapper;

    @Autowired
    private UserSessionRedisUtil userSessionRedisUtil;

    @Value("${authentication.switch}")
    private Boolean isSwitch;

    @Autowired
    private ProjectManagerService projectManagerService;

    @Autowired
    private StatisticsAppletMapper statisticsAppletMapper;

    @Autowired
    private ClientManagementMapper clientManagementMapper;

    @Autowired
    private PfuMapper pfuMapper;

    @Override
    public Page<StatisticsGranaryWebsiteVO> findGranaryByPage(StatisticsGranaryWebsiteVO query) {
        List<String> storageCodeList = toDataPowerList();
        if (null == storageCodeList || storageCodeList.size() == 0) {
            Page<StatisticsGranaryWebsiteVO> voPage = new Page<>();
            return voPage;
        }

        // 获取非资金方的库点
        List<String> isFunderList = toIsFunderList("0");

        Page<StatisticsGranaryWebsiteVO> granaryByPage = statisticsWebsiteMapper.findGranaryByPage(query,storageCodeList);

        List<StatisticsGranaryWebsiteVO> records = granaryByPage.getRecords();
        if (records != null && records.size() > 0) {
            for (StatisticsGranaryWebsiteVO record : records) {
                // 翻译货物名称
                if (StringUtils.isNotBlank(record.getGoodsName())) {
                    record.setGoodsNameStr(statisticsWebsiteMapper.findGoodsNameStr(record.getGoodsName()));
                }

                Double alreadyOutStock = statisticsAppletMapper.findAlreadyOutStock(record.getGranaryAddress(), storageCodeList);
                // 已开出库单数
                record.setAlreadyOutStock(BigDecimal.valueOf(alreadyOutStock));
                Double practicalOutStock = statisticsAppletMapper.findPracticalOutStock(record.getGranaryAddress(), "", storageCodeList);
                // 剩余可出库数量
                record.setResidueOutStockNum(BigDecimal.valueOf(alreadyOutStock).subtract(BigDecimal.valueOf(practicalOutStock)).doubleValue());
                // 库存数量
                record.setGrandTotalNum(BigDecimal.valueOf(record.getGrandTotalInStockNum()).subtract(BigDecimal.valueOf(record.getGrandTotalOutStockNum())).doubleValue());
                // 质押中数量
                record.setJustPledgeNum(BigDecimal.valueOf(record.getGrandTotalPledgeNum()).subtract(BigDecimal.valueOf(record.getGrandTotalRelieveNum())).doubleValue());

                // 解除质押但未提货数量
                if (isFunderList.contains(record.getGranaryAddress())) {
                    record.setNotLadingQualityNum(new Double(0));
                } else {
                    record.setNotLadingQualityNum(BigDecimal.valueOf(record.getGrandTotalRelieveNum()).subtract(BigDecimal.valueOf(record.getGrandTotalOutStockNum())).doubleValue());
                }
                // 已入库未质押数量
                record.setNotPledgeNum(BigDecimal.valueOf(record.getGrandTotalInStockNum()).subtract(BigDecimal.valueOf(record.getGrandTotalPledgeNum())).doubleValue());

                //合作企业
                if (StringUtil.isNotEmpty(record.getPartner())){
                    String partner = record.getPartner();
                    ClientManagementEntity clientManagementEntity = clientManagementMapper.selectById(partner);
                    if (BeanUtil.isNotEmpty(clientManagementEntity)){
                        record.setPartnerStr(clientManagementEntity.getEnterpriseName());
                    }
                }
                // 业务负责人
                if (StringUtil.isNotEmpty(record.getBusinessLeader())){
                    String businessLeader = record.getBusinessLeader();
                    String name = pfuMapper.selectByPId(businessLeader);
                    record.setBusinessLeaderStr(name);
                }
                // 项目负责人
                if (StringUtil.isNotEmpty(record.getProjectLeader())){
                    String projectLeader = record.getProjectLeader();
                    String name = pfuMapper.selectByPId(projectLeader);
                    record.setProjectLeaderStr(name);
                }

                if (StringUtil.isNotEmpty(record.getSupervisor())){
                    String supervisor = record.getSupervisor();
                    StringBuilder supervisorBuilder = new StringBuilder();
                    String[] splitStr = supervisor.split(",");
                    for (int i = 0; i < splitStr.length; i++) {
                        if (StringUtil.isNotEmpty(splitStr[i])){
                            String name = pfuMapper.selectByPId(splitStr[i]);
                            if ( i == splitStr.length - 1){
                                supervisorBuilder.append(name);
                            }else{
                                supervisorBuilder.append(name+",");
                            }
                        }
                    }
                    record.setSupervisorStr(supervisorBuilder.toString());
                }
            }
        }

        return granaryByPage;
    }

    @Override
    public Page<StatisticsHouseWebsiteVO> findHouseByPage(StatisticsHouseWebsiteVO query) {
        // 获取非资金方的库点
        List<String> isFunderList = toIsFunderList("0");

        Page<StatisticsHouseWebsiteVO> houseByPage = statisticsWebsiteMapper.findHouseByPage(query);
        List<StatisticsHouseWebsiteVO> records = houseByPage.getRecords();
        if (records != null && records.size() > 0) {
            for (StatisticsHouseWebsiteVO record : records) {
                // 翻译货物名称
                if (StringUtils.isNotBlank(record.getGoodsName())) {
                    record.setGoodsNameStr(statisticsWebsiteMapper.findGoodsNameStr(record.getGoodsName()));
                }

                Double houseAlreadyOutStock = statisticsAppletMapper.findHouseAlreadyOutStock(record.getGranaryAddress(), record.getHouse(),null);
                // 已开出库单数
                record.setAlreadyOutStock(BigDecimal.valueOf(houseAlreadyOutStock));
                Double practicalOutStock = statisticsAppletMapper.findPracticalOutStock(record.getGranaryAddress(), record.getHouse(),null);
                // 剩余可出库数量
                record.setResidueOutStockNum(BigDecimal.valueOf(houseAlreadyOutStock).subtract(BigDecimal.valueOf(practicalOutStock)).doubleValue());
                // 库存数量
                record.setGrandTotalNum(BigDecimal.valueOf(record.getGrandTotalInStockNum()).subtract(BigDecimal.valueOf(record.getGrandTotalOutStockNum())).doubleValue());
                // 质押中数量
                record.setJustPledgeNum(BigDecimal.valueOf(record.getGrandTotalPledgeNum()).subtract(BigDecimal.valueOf(record.getGrandTotalRelieveNum())).doubleValue());

                // 解除质押但未提货数量
                if (isFunderList.contains(record.getGranaryAddress())) {
                    record.setNotLadingQualityNum(new Double(0));
                } else {
                    record.setNotLadingQualityNum(BigDecimal.valueOf(record.getGrandTotalRelieveNum()).subtract(BigDecimal.valueOf(record.getGrandTotalOutStockNum())).doubleValue());
                }
                // 已入库未质押数量
                record.setNotPledgeNum(BigDecimal.valueOf(record.getGrandTotalInStockNum()).subtract(BigDecimal.valueOf(record.getGrandTotalPledgeNum())).doubleValue());

                //合作企业
                if (StringUtil.isNotEmpty(record.getPartner())){
                    String partner = record.getPartner();
                    ClientManagementEntity clientManagementEntity = clientManagementMapper.selectById(partner);
                    if (BeanUtil.isNotEmpty(clientManagementEntity)){
                        record.setPartnerStr(clientManagementEntity.getEnterpriseName());
                    }
                }
                // 业务负责人
                if (StringUtil.isNotEmpty(record.getBusinessLeader())){
                    String businessLeader = record.getBusinessLeader();
                    String name = pfuMapper.selectByPId(businessLeader);
                    record.setBusinessLeaderStr(name);
                }
                // 项目负责人
                if (StringUtil.isNotEmpty(record.getProjectLeader())){
                    String projectLeader = record.getProjectLeader();
                    String name = pfuMapper.selectByPId(projectLeader);
                    record.setProjectLeaderStr(name);
                }

                if (StringUtil.isNotEmpty(record.getSupervisor())){
                    String supervisor = record.getSupervisor();
                    StringBuilder supervisorBuilder = new StringBuilder();
                    String[] splitStr = supervisor.split(",");
                    for (int i = 0; i < splitStr.length; i++) {
                        if (StringUtil.isNotEmpty(splitStr[i])){
                            String name = pfuMapper.selectByPId(splitStr[i]);
                            if (i==splitStr.length){
                                supervisorBuilder.append(name);
                            }else{
                                supervisorBuilder.append(name+",");
                            }
                        }
                    }
                    record.setSupervisorStr(supervisorBuilder.toString());
                }
            }
        }
        return houseByPage;
    }

    @Override
    public Page<StatisticsHouseDetailsWebsiteVO> findHouseDetailsByPage(StatisticsHouseDetailsWebsiteVO query) {
        Page<StatisticsHouseDetailsWebsiteVO> houseDetailsByPage = statisticsWebsiteMapper.findHouseDetailsByPage(query);
        List<StatisticsHouseDetailsWebsiteVO> records = houseDetailsByPage.getRecords();
        if (records != null && records.size() > 0) {
            for (StatisticsHouseDetailsWebsiteVO record : records) {
                if ("入库".equalsIgnoreCase(record.getBusinessType())) {
                    record.setWeight(
                            BigDecimal.valueOf(record.getGrossWeight())
                                    .subtract(BigDecimal.valueOf(record.getBareWeight()))
                                    .subtract(BigDecimal.valueOf(record.getRealityReduceWeight()))
                    );
                } else {
                    record.setWeight(
                            BigDecimal.valueOf(record.getGrossWeight())
                                    .subtract(BigDecimal.valueOf(record.getBareWeight()))
                    );
                }
            }
        }
        return houseDetailsByPage;
    }


    private List<String> toDataPowerList(){
        PerFrontUserEntity pu = userSessionRedisUtil.getSessionUserObjInfo();
        List<StorageInfoVO> storages = new LinkedList<>();//库点列表
        if (!isSwitch || pu == null) {
            storages = projectManagerService.queryStoragesNoUser();
        } else {
            storages = projectManagerService.queryStoragesHasUser(String.valueOf(pu.getId()));
        }
        List<String> storageCodeList = storages.stream().map(StorageInfoVO::getStorageCode).collect(Collectors.toList());
        return storageCodeList;
    }

    private List<String> toIsFunderList(String isFunder){
       return statisticsWebsiteMapper.findGranaryByIsFunder(isFunder);
    }
}

mapper:

package com.boot.reservation.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.vo.StatisticsGranaryWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseDetailsWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseWebsiteVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface StatisticsWebsiteMapper {

    Page<StatisticsGranaryWebsiteVO> findGranaryByPage(@Param("query") StatisticsGranaryWebsiteVO query, List<String> storageCodeList);

    Page<StatisticsHouseWebsiteVO> findHouseByPage(@Param("query") StatisticsHouseWebsiteVO query);

    Page<StatisticsHouseDetailsWebsiteVO> findHouseDetailsByPage(@Param("query") StatisticsHouseDetailsWebsiteVO query);

    List<String> findGranaryByIsFunder(@Param("isFunder") String isFunder);

    String findGoodsNameStr(String goodsName);
}

Mapper.xml:

翻车事故(与本标题主干无关)

这里先提一下，这个场景下产生的一个翻车事故（与本标题主干无关），是数据统计伴生的一些问题,，这里记录一下，后来人避坑。
在这里插入图片描述
可以看到，这里在统计数据的时候，需要用到很多关联的表，当时写这个sql的时候大意了，直接使用了连接查询。因此便产生了数据查出来与实际数据相差甚远的事情。

举例说明：
由于左连接的特性，如果左边的表有多条编码相同的数据，然后右边的表只有一条数据与左边的表对应，这时候就会产生问题，右边表的一条数据会被拼接到左边每个编码相同的数据后边，就会造成数据重复统计的问题。反过来也一样，右表编码不唯一，即便左边的表用了分组，但是如果右边的表有多条与连接条件匹配的数据，那左边的表也会复制多份，查出来的总数据条数就是，左表数据条乘右表符合条件的数量，这样最右边的表会跟着匹配多份。总之就是，这种问题，使用连接查询的时候不可避免。

由此总结一下，连接查询不适合分组统计数据，查查匹配的编码唯一的数据还行（比如查字典项，已知有且仅有一条数据与查询数据对应），一旦数据不唯一，就会产生问题。就像上边的问题，库可以重复使用，所以左边或者右边的库的编码不可能只出现一次，就产生了问题。

举个栗子:
在这里插入图片描述
此时在这个基础上，无论对左表数据进行统计还是右表数据进行统计，都会有问题

解决办法就是用子查询

下边是正确示例

正确示例:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.boot.reservation.mapper.StatisticsWebsiteMapper">

    <select id="findGranaryByPage"
            parameterType="com.boot.reservation.entity.domain.vo.StatisticsGranaryWebsiteVO"
            resultType="com.boot.reservation.entity.domain.vo.StatisticsGranaryWebsiteVO">
        select
        lps.project_id as project,
        lp.project_name as projectName,
        lps.storage_code as granaryAddress,
        lps.storage_name as granaryAddressName,
        (select sins.kind_name from storage_in_stock sins
            where sins.status in ('02','01') and sins.granary_address = lps.storage_code order by sins.unload_end_time desc limit 0,1) as kindName,
        (select sins.goods_name from storage_in_stock sins
            where sins.status in ('02','01') and sins.granary_address = lps.storage_code order by sins.unload_end_time desc limit 0,1)  as goodsName,
        (select ifnull(sum(sins.settlement_num),0) from storage_in_stock sins
            where sins.status in ('02','01') and sins.granary_address = lps.storage_code) as grandTotalInStockNum,
        (SELECT IFNULL(sum(souts.gross_weight),0) - IFNULL(sum(souts.bare_weight),0)  from storage_out_stock souts
            where souts.granary_address = lps.storage_code AND souts.status in ('02','01')) AS grandTotalOutStockNum,
        (select ifnull(sum(lcl.lading_Quality),0) from storage_receipt sr
            right join storage_pledge sp on (sr.receipt_serial_num = sp.receipt_serial_num and sp.status = '06')
            right join lp_collateral_list lcl on sp.pledge_serial_num = lcl.pledge_number
            where  sr.granary_address = lps.storage_code and sr.status = '06') as grandTotalRelieveNum,
        (select ifnull(sum(sr1.in_num),0) from storage_receipt sr1
            where sr1.receipt_serial_num in (select sp1.receipt_serial_num from storage_pledge sp1 where sp1.status = '06')
            and sr1.granary_address = lps.storage_code and sr1.status = '06') as grandTotalPledgeNum,
        lp.partner as partner,
        lps.business_leader as businessLeader,
        lps.project_leader as projectLeader,
        lps.supervisor as supervisor,
        lps.supervise_start_time as superviseStartTime,
        lps.supervise_end_time as superviseEndTime,
        (select lc.clean_time from lp_cleanstorage lc
            where lc.status = '02' and lc.storage_code = lps.storage_code order by lc.clean_time desc limit 0,1) as cleanTime
        from lp_project_storage lps
        left join lp_project lp on lp.id = lps.project_id
        <where>
            <if test="storageCodeList != null and storageCodeList.size>0">
                lps.storage_code in
                <foreach collection="storageCodeList" item="granaryAddress" open="(" separator="," close=")">
                    #{granaryAddress}
                </foreach>
            </if>
            <if test="query.granaryAddressName != null and query.granaryAddressName != ''">-->
                and lps.storage_name like CONCAT('%',#{query.granaryAddressName},'%')
            </if>
            <if test="query.projectName != null and query.projectName != ''">
                and lps.project_name like CONCAT('%',#{query.projectName},'%')
            </if>
            <if test="query.granaryAddress != null and query.granaryAddress != ''">
                and lps.storage_code = #{query.granaryAddress}
            </if>
            <if test="query.project != null and query.project != ''">
                and lps.project_id = #{query.project}
            </if>
            <if test="query.kindName != null and query.kindName != ''">
                and lps.storage_code in (select sins.granary_address from storage_in_stock sins where sins.status in ('02','01') and sins.kind_name = #{query.kindName})
            </if>
            <if test="query.goodsName != null and query.goodsName != ''">
                and lps.storage_code in (select sins.granary_address from storage_in_stock sins where sins.status in ('02','01') and sins.goods_name = #{query.goodsName})
            </if>
            <if test="query.partner != null and query.partner != ''">
                and lp.partner = #{query.partner}
            </if>
            <if test="query.partnerStr != null and query.partnerStr != ''">
                and lp.partner in (select cm.client_id from client_management cm where cm.enterprise_name like CONCAT('%',#{query.partnerStr},'%'))
            </if>
            <if test="query.businessLeader != null and query.businessLeader != ''">
                and lps.business_leader = #{query.businessLeader}
            </if>
            <if test="query.businessLeaderStr != null and query.businessLeaderStr != ''">
                and lps.business_leader in (select pfu.id from per_front_user pfu where pfu.user_name like CONCAT('%',#{query.businessLeaderStr},'%'))
            </if>
            <if test="query.projectLeader != null and query.projectLeader != ''">
                and lps.project_leader = #{query.projectLeader}
            </if>
            <if test="query.projectLeaderStr != null and query.projectLeaderStr != ''">
                and lps.project_leader in (select pfu.id from per_front_user pfu where pfu.user_name like CONCAT('%',#{query.projectLeaderStr},'%'))
            </if>
            <if test="query.supervisor != null and query.supervisor != ''">
                and lps.supervisor like CONCAT('%',#{query.supervisor},'%')
            </if>
            <if test="query.supervisorStr != null and query.supervisorStr != ''">
                and lps.supervisor in (select pfu.id from per_front_user pfu where pfu.user_name like CONCAT('%',#{query.supervisorStr},'%'))
            </if>
            <if test="query.superviseStartTime != null and query.superviseStartTime != ''">
                and lps.supervise_start_time = #{query.superviseStartTime}
            </if>
            <if test="query.superviseEndTime != null and query.superviseEndTime != ''">
                and lps.supervise_end_time = #{query.superviseEndTime}
            </if>
            <if test="query.cleanTime != null">
                and lps.storage_code in (select lc.storage_code from lp_cleanstorage lc
                                            where lc.status = '02' and DATE_FORMAT(lc.clean_time,'%Y-%m-%d') = #{query.cleanTime})
            </if>
            <if test="query.startTime != null and query.endTime != null">
                AND (lps.storage_code in (
                (select DISTINCT(sins.granary_address) from storage_in_stock sins where
                date_format(sins.bare_weight_time,'%y%m%d %H%i%s') &gt;= date_format(#{query.startTime},'%y%m%d %H%i%s')
                and date_format(sins.bare_weight_time,'%y%m%d %H%i%s') &lt;= date_format(#{query.endTime},'%y%m%d %H%i%s')))
                OR
                lps.storage_code in (
                (select DISTINCT(souts.granary_address) from storage_out_stock souts where
                date_format(souts.gross_weight_time,'%y%m%d %H%i%s') &gt;= date_format(#{query.startTime},'%y%m%d %H%i%s')
                and date_format(souts.gross_weight_time,'%y%m%d %H%i%s') &lt;= date_format(#{query.endTime},'%y%m%d %H%i%s'))))
            </if>
        </where>
        order by lp.create_time desc
    </select>

    <select id="findHouseByPage"
            parameterType="com.boot.reservation.entity.domain.vo.StatisticsHouseWebsiteVO"
            resultType="com.boot.reservation.entity.domain.vo.StatisticsHouseWebsiteVO">
        select
        lps.storage_code as granaryAddress,
        lpsh.storehouse_code as house,
        lpsh.storehouse_name as houseName,
        (select sins.kind_name from storage_in_stock sins
            where sins.status in ('02','01') and sins.house = lpsh.storehouse_code order by sins.unload_end_time desc limit 0,1) as kindName,
        (select sins.goods_name from storage_in_stock sins
            where sins.status in ('02','01') and sins.house = lpsh.storehouse_code order by sins.unload_end_time desc limit 0,1)  as goodsName,
        (select ifnull(sum(sins.settlement_num),0) from storage_in_stock sins
            where sins.status in ('02','01') and sins.house = lpsh.storehouse_code) as grandTotalInStockNum,
        (SELECT IFNULL(sum(souts.gross_weight), 0) - IFNULL(sum(souts.bare_weight),0) from storage_out_stock souts
            where souts.house = lpsh.storehouse_code and souts.status in ('02','01'))  AS grandTotalOutStockNum,
        (select ifnull(sum(lcl.lading_Quality),0) from storage_receipt sr
            right join storage_pledge sp on (sr.receipt_serial_num = sp.receipt_serial_num and sp.status = '06')
            right join lp_collateral_list lcl on sp.pledge_serial_num = lcl.pledge_number
            where  sr.house = lpsh.storehouse_code and sr.status = '06') as grandTotalRelieveNum,
        (select ifnull(sum(sr1.in_num),0) from storage_receipt sr1
            where sr1.receipt_serial_num in (select sp1.receipt_serial_num from storage_pledge sp1 where sp1.status = '06')
            and sr1.house = lpsh.storehouse_code and sr1.status = 06) as grandTotalPledgeNum,
        lp.partner as partner,
        lps.business_leader as businessLeader,
        lps.project_leader as projectLeader,
        lps.supervisor as supervisor,
        lps.supervise_start_time as superviseStartTime,
        lps.supervise_end_time as superviseEndTime,
        (select lc.clean_time from lp_cleanstorage lc
        left join lp_cleanstorage_details lcd on lcd.clean_id = lc.id
        where lc.status = '02' and lcd.storagehouse_code = lpsh.storehouse_code order by lc.clean_time desc limit 0,1) as cleanTime,
        lpsh.status as status
        from lp_project_storagehouse lpsh
        left join lp_project_storage lps on lps.storage_code = lpsh.storage_Code
        left join lp_project lp on lp.id = lps.project_id
        <where>
            <if test="query.granaryAddress != null and query.granaryAddress != ''">
                lps.storage_code = #{query.granaryAddress}
            </if>
            <if test="query.house != null and query.house != ''">
                and lpsh.storehouse_code = #{query.house}
            </if>
            <if test="query.houseName != null and query.houseName != ''">
                and lpsh.storehouse_name like CONCAT('%',#{query.houseName},'%')
            </if>
            <if test="query.kindName != null and query.kindName != ''">
                and lpsh.storehouse_code in (select sins.house from storage_in_stock sins where sins.status in ('02','01') and sins.kind_name = #{query.kindName})
            </if>
            <if test="query.goodsName != null and query.goodsName != ''">
                and lpsh.storehouse_code in (select sins.house from storage_in_stock sins where sins.status in ('02','01') and sins.goods_name = #{query.goodsName})
            </if>
            <if test="query.partner != null and query.partner != ''">
                and lp.partner = #{query.partner}
            </if>
            <if test="query.partnerStr != null and query.partnerStr != ''">
                and lp.partner in (select cm.client_id from client_management cm where cm.enterprise_name like CONCAT('%',#{query.partnerStr},'%'))
            </if>
            <if test="query.businessLeader != null and query.businessLeader != ''">
                and lps.business_leader = #{query.businessLeader}
            </if>
            <if test="query.businessLeaderStr != null and query.businessLeaderStr != ''">
                and lps.business_leader in (select pfu.id from per_front_user pfu where pfu.user_name like CONCAT('%',#{query.businessLeaderStr},'%'))
            </if>
            <if test="query.projectLeader != null and query.projectLeader != ''">
                and lps.project_leader = #{query.projectLeader}
            </if>
            <if test="query.projectLeaderStr != null and query.projectLeaderStr != ''">
                and lps.project_leader in (select pfu.id from per_front_user pfu where pfu.user_name like CONCAT('%',#{query.projectLeaderStr},'%'))
            </if>
            <if test="query.supervisor != null and query.supervisor != ''">
                and lps.supervisor like CONCAT('%',#{query.supervisor},'%')
            </if>
            <if test="query.supervisorStr != null and query.supervisorStr != ''">
                and lps.supervisor in (select pfu.id from per_front_user pfu where pfu.user_name like CONCAT('%',#{query.supervisorStr},'%'))
            </if>
            <if test="query.superviseStartTime != null and query.superviseStartTime != ''">
                and lps.supervise_start_time = #{query.superviseStartTime}
            </if>
            <if test="query.superviseEndTime != null and query.superviseEndTime != ''">
                and lps.supervise_end_time = #{query.superviseEndTime}
            </if>
            <if test="query.cleanTime != null">
                and lpsh.storehouse_code in (select lcd.storagehouse_code from lp_cleanstorage_details lcd
                                            left join lp_cleanstorage lc on lc.id = lcd.clean_id
                                            where lc.status = '02' and DATE_FORMAT(lc.clean_time,'%Y-%m-%d') = #{query.cleanTime})
            </if>
            <if test="query.status != null and query.status != ''">
                lpsh.status = #{query.status}
            </if>
            <if test="query.startTime != null and query.endTime != null">
                AND (lps.storage_code in (
                (select DISTINCT(sins.granary_address) from storage_in_stock sins where
                date_format(sins.bare_weight_time,'%y%m%d %H%i%s') &gt;= date_format(#{query.startTime},'%y%m%d %H%i%s')
                and date_format(sins.bare_weight_time,'%y%m%d %H%i%s') &lt;= date_format(#{query.endTime},'%y%m%d %H%i%s')))
                OR
                lps.storage_code in (
                (select DISTINCT(souts.granary_address) from storage_out_stock souts where
                date_format(souts.gross_weight_time,'%y%m%d %H%i%s') &gt;= date_format(#{query.startTime},'%y%m%d %H%i%s')
                and date_format(souts.gross_weight_time,'%y%m%d %H%i%s') &lt;= date_format(#{query.endTime},'%y%m%d %H%i%s'))))
            </if>
        </where>
        order by str_to_date(lpsh.time, '%Y-%m-%d %H:%i:%S') desc
    </select>

    <select id="findHouseDetailsByPage"
            parameterType="com.boot.reservation.entity.domain.vo.StatisticsHouseDetailsWebsiteVO"
            resultType="com.boot.reservation.entity.domain.vo.StatisticsHouseDetailsWebsiteVO">
        select
        *
        from
        (
        (select sins.granary_address as granaryAddress,
        sins.house as house,
        sins.in_stock_serial_num as serialNumber,
        '入库' as businessType,
        sins.unload_end_time as time,
        sins.kind_name as kindName,
        sins.gross_weight as grossWeight,
        sins.bare_weight as bareWeight,
        sins.reality_reduce_weight as realityReduceWeight
        from storage_in_stock sins
        where sins.status = 02
        <if test="query.house != null and query.house != ''">
            and sins.house = #{query.house}
        </if>)
        UNION ALL
        (select souts.granary_address as granaryAddress,
        souts.house as house,
        souts.out_stock_serial_num as serialNumber,
        '出库' as businessType,
        souts.load_end_time as time,
        souts.goods_name as kindName,
        souts.gross_weight as grossWeight,
        souts.bare_weight as bareWeight,
        0.0 as realityReduceWeight
        from storage_out_stock souts
        where souts.status = 02
        <if test="query.house != null and query.house != ''">
            and souts.house = #{query.house}
        </if>)
        ) as shdw
        <where>
            <if test="query.businessType != null and query.businessType != ''">
                and shdw.businessType = #{query.businessType}
            </if>
            <if test="query.kindName != null and query.kindName != ''">
                and shdw.kindName = #{query.kindName}
            </if>
            <if test="query.startTime != null">
                AND date_format(shdw.time,'%y%m%d %H%i%s') &gt;= date_format(#{query.startTime},'%y%m%d %H%i%s')
            </if>
            <if test="query.endTime != null">
                AND date_format(shdw.time,'%y%m%d %H%i%s') &lt;= date_format(#{query.endTime},'%y%m%d %H%i%s')
            </if>
        </where>
        ORDER BY time desc

    </select>

    <select id="findGranaryByIsFunder" resultType="String">
        select lps.storage_code
        from lp_project_storage lps
            left join lp_project lp on lp.id = lps.project_id
        where lp.isfunder = #{isFunder}
    </select>

    <select id="findGoodsNameStr" resultType="String" parameterType="String">
        select cargo_name from lp_cargo where cargo_code = #{goodsName}
    </select>
</mapper>

以下是实体类:

package com.boot.reservation.entity.domain.vo;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.DictValue;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;


/**
 * 库点统计VO
 */
@Data
@ApiModel("库点统计VO")
public class StatisticsGranaryWebsiteVO extends Page implements Serializable {

    private static final long serialVersionUID = 1L;


    /**
     * 项目id
     */
    @ApiModelProperty("项目id")
    private String project;
    /**
     * 项目名称
     */
    @ApiModelProperty("项目名称")
    private String projectName;
    /**
     * 库点编号
     */
    @ApiModelProperty("库点编号")
    private String granaryAddress;
    /**
     * 库点名称
     */
    @ApiModelProperty("库点名称")
    private String granaryAddressName;
    /**
     * 货物类型
     */
    @ApiModelProperty("货物类型")
    @DictValue(dictCode = "cargoType")
    private String kindName;
    /**
     * 商品名称
     */
    @ApiModelProperty("商品名称")
    private String goodsName;
    /**
     * 商品名称文字
     */
    @ApiModelProperty("商品名称文字")
    private String goodsNameStr;
    /**
     * 累计入库数量（kg）
     */
    @ApiModelProperty("累计入库数量（kg）")
    private Double grandTotalInStockNum;
    /**
     * 累计出库数量（kg）
     */
    @ApiModelProperty("累计出库数量（kg）")
    private Double grandTotalOutStockNum;
    /**
     * 累计解压数量（kg）
     */
    @ApiModelProperty("累计解压数量（kg）")
    private Double grandTotalRelieveNum;
    /**
     * 剩余可出库数量（kg）
     */
    @ApiModelProperty("剩余可出库数量（kg）")
    private Double residueOutStockNum;
    /**
     * 库存数量（kg）
     */
    @ApiModelProperty("库存数量（kg）")
    private Double grandTotalNum;
    /**
     * 累计质押数量（kg）
     */
    @ApiModelProperty("累计质押数量（kg）")
    private Double grandTotalPledgeNum;
    /**
     * 质押中数量（kg）
     */
    @ApiModelProperty("质押中数量（kg）")
    private Double justPledgeNum;
    /**
     * 解除质押但未提货数量（kg）
     */
    @ApiModelProperty("解除质押但未提货数量（kg）")
    private Double notLadingQualityNum;
    /**
     * 已入库未质押数量（kg）
     */
    @ApiModelProperty("已入库未质押数量（kg）")
    private Double notPledgeNum;
    /**
     * 合作企业
     */
    @ApiModelProperty("合作企业")
    private String partner;
    /**
     * 合作企业文字
     */
    @ApiModelProperty("合作企业文字")
    private String partnerStr;
    /**
     * 业务负责人
     */
    @ApiModelProperty("业务负责人")
    private String businessLeader;
    /**
     * 业务负责人文字
     */
    @ApiModelProperty("业务负责人文字")
    private String businessLeaderStr;
    /**
     * 项目负责人
     */
    @ApiModelProperty("项目负责人")
    private String projectLeader;
    /**
     * 项目负责人文字
     */
    @ApiModelProperty("项目负责人文字")
    private String projectLeaderStr;
    /**
     * 监管员
     */
    @ApiModelProperty("监管员")
    private String supervisor;
    /**
     * 监管员文字
     */
    @ApiModelProperty("监管员文字")
    private String supervisorStr;
    /**
     * 监管开始时间
     */
    @ApiModelProperty("监管开始时间")
    private String superviseStartTime;
    /**
     * 预计结束时间
     */
    @ApiModelProperty("预计结束时间")
    private String superviseEndTime;
    /**
     * 清库时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd")
    @ApiModelProperty("清库时间")
    private Date cleanTime;
    /**
     * 已开出库单数
     */
    @ApiModelProperty("已开出库单数")
    private BigDecimal alreadyOutStock;

    /**
     * 业务开始时间
     */
    @ApiModelProperty("业务开始时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date startTime;
    /**
     * 业务结束时间
     */
    @ApiModelProperty("业务结束时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date endTime;
}

package com.boot.reservation.entity.domain.vo;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.DictValue;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;


/**
 * 仓房统计VO
 */
@Data
@ApiModel("仓房统计VO")
public class StatisticsHouseWebsiteVO extends Page implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 库点编号
     */
    @ApiModelProperty("库点编号")
    private String granaryAddress;
    /**
     * 仓房编码
     */
    @ApiModelProperty("仓房编码")
    private String house;
    /**
     * 仓房名称
     */
    @ApiModelProperty("仓房名称")
    private String houseName;
    /**
     * 货物类型
     */
    @ApiModelProperty("货物类型")
    @DictValue(dictCode = "cargoType")
    private String kindName;
    /**
     * 商品名称
     */
    @ApiModelProperty("商品名称")
    private String goodsName;
    /**
     * 商品名称文字
     */
    @ApiModelProperty("商品名称文字")
    private String goodsNameStr;
    /**
     * 累计入库数量（kg）
     */
    @ApiModelProperty("累计入库数量（kg）")
    private Double grandTotalInStockNum;
    /**
     * 累计出库数量（kg）
     */
    @ApiModelProperty("累计出库数量（kg）")
    private Double grandTotalOutStockNum;
    /**
     * 累计解压数量（kg）
     */
    @ApiModelProperty("累计解压数量（kg）")
    private Double grandTotalRelieveNum;
    /**
     * 剩余可出库数量（kg）
     */
    @ApiModelProperty("剩余可出库数量（kg）")
    private Double residueOutStockNum;
    /**
     * 库存数量（kg）
     */
    @ApiModelProperty("库存数量（kg）")
    private Double grandTotalNum;
    /**
     * 累计质押数量（kg）
     */
    @ApiModelProperty("累计质押数量（kg）")
    private Double grandTotalPledgeNum;
    /**
     * 质押中数量（kg）
     */
    @ApiModelProperty("质押中数量（kg）")
    private Double justPledgeNum;
    /**
     * 解除质押但未提货数量（kg）
     */
    @ApiModelProperty("解除质押但未提货数量（kg）")
    private Double notLadingQualityNum;
    /**
     * 已入库未质押数量（kg）
     */
    @ApiModelProperty("已入库未质押数量（kg）")
    private Double notPledgeNum;
    /**
     * 合作企业
     */
    @ApiModelProperty("合作企业")
    private String partner;
    /**
     * 合作企业文字
     */
    @ApiModelProperty("合作企业文字")
    private String partnerStr;
    /**
     * 业务负责人
     */
    @ApiModelProperty("业务负责人")
    private String businessLeader;
    /**
     * 业务负责人文字
     */
    @ApiModelProperty("业务负责人文字")
    private String businessLeaderStr;
    /**
     * 项目负责人
     */
    @ApiModelProperty("项目负责人")
    private String projectLeader;
    /**
     * 项目负责人文字
     */
    @ApiModelProperty("项目负责人文字")
    private String projectLeaderStr;
    /**
     * 监管员
     */
    @ApiModelProperty("监管员")
    private String supervisor;
    /**
     * 监管员文字
     */
    @ApiModelProperty("监管员文字")
    private String supervisorStr;
    /**
     * 监管开始时间
     */
    @ApiModelProperty("监管开始时间")
    private String superviseStartTime;
    /**
     * 预计结束时间
     */
    @ApiModelProperty("预计结束时间")
    private String superviseEndTime;
    /**
     * 清库时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd")
    @ApiModelProperty("清库时间")
    private Date cleanTime;
    /**
     * 已开出库单数
     */
    @ApiModelProperty("已开出库单数")
    private BigDecimal alreadyOutStock;
    /**
     * 仓房状态2
     */
    @ApiModelProperty("仓房状态")
    @DictValue(dictCode = "storagehouseStatus")
    private String status;

    /**
     * 业务开始时间
     */
    @ApiModelProperty("业务开始时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date startTime;
    /**
     * 业务结束时间
     */
    @ApiModelProperty("业务结束时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date endTime;
}

package com.boot.reservation.entity.domain.vo;

import java.math.BigDecimal;
import java.util.Date;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.DictValue;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;


/**
 * 仓房明细查看统计VO
 */
@Data
@ApiModel("仓房明细查看统计VO")
public class StatisticsHouseDetailsWebsiteVO extends Page implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 仓房编码
     */
    @ApiModelProperty("仓房编码")
    private String house;
    /**
     * 库点编号
     */
    @ApiModelProperty("库点编号")
    private String granaryAddress;
    /**
     * 流水号
     */
    @ApiModelProperty("流水号")
    private String serialNumber;
    /**
     * 业务类型
     */
    @ApiModelProperty("业务类型")
    private String businessType;
    /**
     * 时间
     */
    @ApiModelProperty("时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date time;
    /**
     * 业务开始时间
     */
    @ApiModelProperty("业务开始时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date startTime;
    /**
     * 业务结束时间
     */
    @ApiModelProperty("业务结束时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date endTime;
    /**
     * 货物类型
     */
    @ApiModelProperty("货物类型")
    @DictValue(dictCode = "cargoType")
    private String kindName;
    /**
     * 商品净重
     */
    @ApiModelProperty("商品净重")
    private BigDecimal weight;
    /**
     * 毛重 - 与前端无关
     */
    @ApiModelProperty("毛重 - 与前端无关")
    private Double grossWeight;
    /**
     * 皮重 - 与前端无关
     */
    @ApiModelProperty("皮重 - 与前端无关")
    private Double bareWeight;
    /**
     * 实际扣量 - 与前端无关
     */
    @ApiModelProperty("实际扣量 - 与前端无关")
    private Double realityReduceWeight;
}