简单sql不要拆分更细粒度的查询

我经常听开发组长老大说不要在mapper层写非常复杂的sql，这种sql写的不能复用，可读性差，难维护（主要这公司的原始代码的sql写的都快成为了存储过程）
所以我这次就全部写的很简单sql，逻辑全写在service层。
出错代码（被注释）

package com.sf.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.sf.bean.Area;
import com.sf.bean.T_WADay;
import com.sf.dao.*;
import com.sf.vo.AreaToGrossVo;
import com.sf.vo.MeterToNetworkVo;
import groovy.util.IFileNameFinder;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Author: bi xuan
 * @Date: 2021/7/24 14:08
 * @Description: 整理用水分类数据，按照生活，商业，公共等用水类型进行分类
 **/
@Service
public class WaterStyleService {
    @Resource
    private NetWorkMeterDAO netWorkMeterDAO;

    @Resource
    private NetWorkDAO netWorkDAO;

    @Resource
    private T_WADayDAO t_waDayDAO;

    @Resource
    private TexingValueDAO texingValueDAO;

    @Resource
    private CommonDAO commonDAO;

    @Resource
    private AreaDAO areaDAO;

    /**
     * 查询当前月份
     *
     * @param year
     * @param month
     * @return
     */
    public List<AreaToGrossVo> manage(Integer year,Integer month) {
        //找出特性为7的出水表
        List<MeterToNetworkVo> meterAndNetwork = commonDAO.getMeterAndNetwork();
        //获取所有的区域
        List<Integer> areaIds = meterAndNetwork.stream().map(MeterToNetworkVo::getArea_ID).distinct().collect(Collectors.toList());
        List<AreaToGrossVo> areaToGrossVos = new ArrayList<>();
        DecimalFormat df = new DecimalFormat("#0.00");
        for (Integer areaId : areaIds) {
            //获取教学类型的水表列表
            List<Integer> teachMeters = meterAndNetwork.stream().filter(x -> "1".equals(x.getTexingValue())&&x.getArea_ID().equals(areaId)).map(MeterToNetworkVo::getMeter_ID).collect(Collectors.toList());
            //获取公共类型的水表列表
            List<Integer> commonMeters = meterAndNetwork.stream().filter(x -> "2".equals(x.getTexingValue())&&x.getArea_ID().equals(areaId)).map(MeterToNetworkVo::getMeter_ID).collect(Collectors.toList());
            //获取生活类型的水表列表
            List<Integer> lifeMeters = meterAndNetwork.stream().filter(x -> "2".equals(x.getTexingValue())&&x.getArea_ID().equals(areaId)).map(MeterToNetworkVo::getMeter_ID).collect(Collectors.toList());
            //获取商业类型的水表列表
            List<Integer> businessMeters = meterAndNetwork.stream().filter(x -> "4".equals(x.getTexingValue())&&x.getArea_ID().equals(areaId)).map(MeterToNetworkVo::getMeter_ID).collect(Collectors.toList());

            AreaToGrossVo areaToGross = new AreaToGrossVo();
            areaToGross.setAreaID(areaId);
//            LambdaQueryWrapper<Area> areaLambdaQueryWrapper = new LambdaQueryWrapper<Area>().eq(Area::getAreaID,areaId);
            Area area = areaDAO.selectById(areaId);
//            Area area = areaDAO.selectOne(areaLambdaQueryWrapper);
            areaToGross.setAreaName(area.getAreaName());
            if (teachMeters.isEmpty()) {
                areaToGross.setTeachValue("0.0");
            } else {
                //所有教学类型表的用量总和
//                Double allTeachMeterSum = 0.0;
//                for (Integer teachMeter : teachMeters) {
//                    LambdaQueryWrapper<T_WADay> eq = new LambdaQueryWrapper<T_WADay>().eq(T_WADay::getMeterID, teachMeter).eq(T_WADay::getSelectYear,year).eq(T_WADay::getSelectMonth,month);
//                    List<T_WADay> t_waDays = t_waDayDAO.selectList(eq);
//                    //如果数据集为空
//                    if (t_waDays.isEmpty()) {
//                        areaToGross.setTeachValue("0.0");
//                    } else {
//                        //教学类型下的某只单表的所有日期下的总和
//                        double sum = t_waDays.stream().mapToDouble(T_WADay::getZGross).sum();
//                        allTeachMeterSum += sum;
//                    }
//                }

                Double allTeachMeterSum = commonDAO.getGrossByArea(year, month, teachMeters);
                areaToGross.setTeachValue(df.format(allTeachMeterSum));
            }

            if (commonMeters.isEmpty()) {
                areaToGross.setCommonValue("0.0");
            } else {
//                Double allCommonMeterSum = 0.0;
//                for (Integer commonMeter : commonMeters) {
//                    LambdaQueryWrapper<T_WADay> eq = new LambdaQueryWrapper<T_WADay>().eq(T_WADay::getMeterID, commonMeter).eq(T_WADay::getSelectYear,year).eq(T_WADay::getSelectMonth,month);
//                    List<T_WADay> t_waDays = t_waDayDAO.selectList(eq);
//                    //如果数据集为空
//                    if (t_waDays.isEmpty()) {
//                        areaToGross.setTeachValue("0.0");
//                    } else {
//                        double sum = t_waDays.stream().mapToDouble(T_WADay::getZGross).sum();
//                        allCommonMeterSum += sum;
//                    }
//                }
                Double allCommonMeterSum = commonDAO.getGrossByArea(year, month, teachMeters);
                areaToGross.setCommonValue(df.format(allCommonMeterSum));
            }

            if (lifeMeters.isEmpty()) {
                areaToGross.setLifeValue("0.0");
            } else {
//                Double allLifeMeterSum = 0.0;
//                for (Integer lifeMeter : lifeMeters) {
//                    LambdaQueryWrapper<T_WADay> eq = new LambdaQueryWrapper<T_WADay>().eq(T_WADay::getMeterID, lifeMeter).eq(T_WADay::getSelectYear,year).eq(T_WADay::getSelectMonth,month);
//                    List<T_WADay> t_waDays = t_waDayDAO.selectList(eq);
//                    //如果数据集为空
//                    if (t_waDays.isEmpty()) {
//                        areaToGross.setTeachValue("0.0");
//                    } else {
//                        double sum = t_waDays.stream().mapToDouble(T_WADay::getZGross).sum();
//                        allLifeMeterSum += sum;
//                    }
//                }
                Double allLifeMeterSum = commonDAO.getGrossByArea(year, month, teachMeters);
                areaToGross.setLifeValue(df.format(allLifeMeterSum));
            }

            if (businessMeters.isEmpty()) {
                areaToGross.setBusinessValue("0.0");
            } else {
//                Double allBusinessMeterSum = 0.0;
//                for (Integer businessMeter : businessMeters) {
//                    LambdaQueryWrapper<T_WADay> eq = new LambdaQueryWrapper<T_WADay>().eq(T_WADay::getMeterID, businessMeter).eq(T_WADay::getSelectYear,year).eq(T_WADay::getSelectMonth,month);
//                    List<T_WADay> t_waDays = t_waDayDAO.selectList(eq);
//                    //如果数据集为空
//                    if (t_waDays.isEmpty()) {
//                        areaToGross.setTeachValue("0.0");
//                    } else {
//                        double sum = t_waDays.stream().mapToDouble(T_WADay::getZGross).sum();
//                        allBusinessMeterSum += sum;
//                    }
//                }
                Double allBusinessMeterSum = commonDAO.getGrossByArea(year, month, teachMeters);
                areaToGross.setBusinessValue(df.format(allBusinessMeterSum));
            }
            areaToGrossVos.add(areaToGross);
        }
        return areaToGrossVos;
    }
}

    <select id="getGrossByArea" resultType="java.lang.Double">
        SELECT sum(ZGross) as gross FROM t_waday WHERE Meter_ID IN <foreach collection="meterIds" item="meterId" open="(" separator="," close=")">
            #{meterId}
    </foreach> AND SelectYear = #{year} AND SelectMonth = #{month}
    </select>

发现一个sql可以代替在代码里写循环，修改后的代码运行速度

image.png

修改之前速度:

image.png 企业微信截图_16272914143625.png

其实我们的结论：我们都知道mysql的联表查询性能没有其他的sql强大，似乎pgsql的联表查询的性能最好，所以我们应该写很多表的联表的查询sql，而且在阿里巴巴的开发手册中也明示，mysql紧张超过三表的联查，但是mysql的优势就是他的单表查询能力，所以不用担心单表查询写了十分复杂的sql