如何将一个按天统计的sql批量查询？

xuanlvhaoshao

2020-05-07

最近遇到一个需求，要按天统计一些数据，但之前系统已经运行大半年了，需要把之前的数据也做一个统计：

于是刚开始我是这样写的：

public void autoSaveData(RentalCostReportQueryVO queryVo) {
        Date startTime;
        Date endTime;
        if (queryVo.getStartTime() == null || queryVo.getEndTime() == null) {
            startTime = DateUtils.getCurrentDate();
            endTime = DateUtils.getCurrentDate();
        } else {
            startTime = queryVo.getStartTime();
            endTime = queryVo.getEndTime().before(DateUtils.getCurrentDate()) ? queryVo.getEndTime() : DateUtils.getCurrentDate();
        }

        Calendar startCalendar = Calendar.getInstance();
        Calendar endCalendar = Calendar.getInstance();
        startCalendar.setTime(startTime);
        endCalendar.setTime(endTime);
        List<RentalCostReport> rentalCostReportList = new ArrayList<>();
        List<Date> dateList = new ArrayList<>();
        do {
            Date currentTime = startCalendar.getTime();　　　　　　　//此处是循环执行的查询
            List<RentalCostReport> rentalCostReportList1 = rentalCostMapper.queryRentalCostByDay(currentTime);
//日期加1
            startCalendar.add(Calendar.DAY_OF_MONTH, 1);
            //当前日期和结束日历日期比较，超过结束日期则终止
        } while (!startCalendar.after(endCalendar));
        
        List<RentalCostReport> rentalCostReportList2 = rentalCostMapper.queryFmAttendanceBlock(queryVo)
                .stream().filter(item -> null != item.getId()).collect(Collectors.toList());
        List<RentalCostReport> rentalCostReportList3 = rentalCostMapper.queryStoreAndDeliverReport(queryVo);
        mergeRentalCostReportList(rentalCostReportList, rentalCostReportList2, rentalCostReportList3);
        if (CollectionUtils.isNotEmpty(rentalCostReportList)) {
            for (RentalCostReport rentalCostReport : rentalCostReportList) {
                rentalCostReport.setId(rentalCostReport.getId() + rentalCostReport.getLeaseCode());
            }
            rentalCostMapper.batchSaveOrUpdate(rentalCostReportList);
        }
    }

其实很简单就是把某个时间段内遍历出具体是哪一天，然后把这个具体的天当做参数传递到mapper中，然后就循环查出每一天的集合，再把集合累加

　　　　do {
            Date currentTime = startCalendar.getTime();　　　　　　　//此处是循环执行的查询
            List<RentalCostReport> rentalCostReportList1 = rentalCostMapper.queryRentalCostByDay(currentTime);
            
            //日期加1
            startCalendar.add(Calendar.DAY_OF_MONTH, 1);
            //当前日期和结束日历日期比较，超过结束日期则终止
        } while (!startCalendar.after(endCalendar));

这是一种做法，但这种做法很耗费时间，比如我要查询一年按天的统计就会循环执行365次，很耗费时间，于是我就在想，怎样能够批量传参（List<Date> 这个集合当中包含了具体的日期）然后批量返回结果

思路：在sql中有个UNION 关键字他是把相同结构的字段合并返回

比如 SELECT #{currentDate} dateTime FROM A WHERE begin_time <= #{currentTime} AND end_time >= #{currentTime}

#{currentTime} 代表的是由mybatis传进来的参数比如我传参进来是 ‘2020-05-04’ 但这个只能查询一天的

SELECT ‘2020-05-04’ dateTime FROM A WHERE begin_time <= ‘2020-05-04’ AND end_time >= ‘2020-05-04’

SELECT ‘2020-05-05’ dateTime FROM A WHERE begin_time <= ‘2020-05-05’ AND end_time >= ‘2020-05-05’

如上我如果要查询 ‘2020-05-05’ 就需要循环查询一次

但我用UNION 把他们连接起来呢？

SELECT ‘2020-05-04’ dateTime FROM A WHERE begin_time <= ‘2020-05-04’ AND end_time >= ‘2020-05-04’

UNION

SELECT ‘2020-05-05’ dateTime FROM A WHERE begin_time <= ‘2020-05-05’ AND end_time >= ‘2020-05-05’

这样就可以一次性查出来了

由此悟出来我可以使用Mybatis的 foreach 标签来循环一个集合用UNION把他们联合起来，就形成了批量查询：

<select id="batchQueryRentalCostByDay"            　　　　resultType="com.hope.saas.wms.entity.decision.report.RentalCostReport">
        <foreach collection="list" item="currentTime" index="index" separator=" UNION ">
            SELECT
            CONCAT(
            a.tenant_id,
            b.temperature_layer_code,
            STR_TO_DATE( #{currentTime}, ‘%Y-%m-%d‘ )) id,
            STR_TO_DATE( #{currentTime}, ‘%Y-%m-%d‘ ) dateTime,
            b.temperature_layer_name temperatureLayerName,
            b.temperature_layer_code temperatureLayerCode,
            a.tenant_id tenantId,
            a.tenant_name tenantName
            FROM
            warehouse_lease_contract a
            LEFT JOIN leasing_information b ON a.id = b.warehouse_lease_contract_id
            WHERE
            begin_time &lt;= #{currentTime} AND end_time >= #{currentTime}
            AND status_code = 1
            GROUP BY
            lease_code,
            b.temperature_layer_code,
            a.tenant_id
        </foreach>
    </select>

运行起来效率相当快！

sql日期 date