SQL计算平均的平均值及全连接语句，关联订单详情映射JavaDTO

我来给你梳理一下具体的实现步骤，既要保留你计算好的月均汽油用量，又能带出所有订单详情，方便后续用Java的RowMapper做一对多映射。

一、SQL语句调整 #

根据你描述的“先按日期分组算每月平均，再外层算月均平均值”的逻辑，我把SQL改成下面这样（以MySQL为例，不同数据库的日期格式化函数可自行替换，比如Oracle用TO_CHAR，SQL Server用FORMAT）：

SELECT 
    t.truckId,
    t.truckcode,
    o.purchasedDate,
    o.descriptions,
    o.petrolQty, -- 单条订单的汽油用量
    truck_monthly_avg.avgPerMonth -- 保留计算好的月均数值
FROM 
    truck t
INNER JOIN 
    `order` o ON t.truckId = o.truckId
INNER JOIN (
    -- 核心计算逻辑：先算每月平均，再算该卡车的月均平均值
    SELECT 
        truckId,
        AVG(monthly_avg) AS avgPerMonth
    FROM (
        SELECT 
            truckId,
            DATE_FORMAT(purchasedDate, '%Y-%m') AS order_month,
            AVG(petrolQty) AS monthly_avg
        FROM `order`
        GROUP BY truckId, order_month
    ) monthly_calc
    GROUP BY truckId
) truck_monthly_avg ON t.truckId = truck_monthly_avg.truckId
-- 可选：如需过滤特定卡车或时间范围，在此添加WHERE条件

这个SQL的逻辑是：

1. 最内层子查询按「卡车+月份」分组，算出每个卡车每个月的汽油平均用量
2. 中间层基于内层结果，按卡车分组算出该卡车的月均平均值（也就是你要保留的目标数值）
3. 最后关联卡车表、订单表和中间层的月均结果，让每一条订单数据都带上对应卡车的月均数值，结果集是扁平结构，方便后续RowMapper处理。

二、Java RowMapper实现一对多映射 #

首先定义你的DTO结构，明确主DTO和订单详情DTO的层级关系：

// 主DTO：包含卡车信息、月均数值和订单列表
public class TruckPetrolStatsDTO {
    private Integer truckId;
    private String truckcode;
    private BigDecimal avgPerMonth;
    private List<OrderDetail> orderDetails = new ArrayList<>();

    // 构造器、getter、setter按需添加，这里省略
}

// 订单详情DTO
public class OrderDetail {
    private LocalDate purchasedDate;
    private String descriptions;
    private BigDecimal petrolQty;

    // 构造器、getter、setter按需添加，这里省略
}

然后实现RowMapper，这里要注意：结果集里每一行都是同一个卡车的不同订单，所以需要用缓存Map避免重复创建主DTO，把同一个卡车的订单合并到同一个DTO的订单列表中：

public class TruckPetrolStatsRowMapper implements RowMapper<TruckPetrolStatsDTO> {
    // 用Map缓存已处理的卡车DTO，key为truckId
    private final Map<Integer, TruckPetrolStatsDTO> dtoCache = new HashMap<>();

    @Override
    public TruckPetrolStatsDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
        Integer truckId = rs.getInt("truckId");
        // 先从缓存取，没有则新建主DTO
        TruckPetrolStatsDTO statsDTO = dtoCache.get(truckId);
        if (statsDTO == null) {
            statsDTO = new TruckPetrolStatsDTO();
            statsDTO.setTruckId(truckId);
            statsDTO.setTruckcode(rs.getString("truckcode"));
            statsDTO.setAvgPerMonth(rs.getBigDecimal("avgPerMonth"));
            dtoCache.put(truckId, statsDTO);
        }
        // 构造订单详情并添加到主DTO的列表中
        OrderDetail orderDetail = new OrderDetail();
        orderDetail.setPurchasedDate(rs.getDate("purchasedDate").toLocalDate());
        orderDetail.setDescriptions(rs.getString("descriptions"));
        orderDetail.setPetrolQty(rs.getBigDecimal("petrolQty"));
        statsDTO.getOrderDetails().add(orderDetail);
        return statsDTO;
    }

    // 使用提示：JdbcTemplate查询后会返回重复的主DTO（每一行对应一个），需去重
    // 最终结果可通过以下方式获取：
    // List<TruckPetrolStatsDTO> finalResult = new ArrayList<>(dtoCache.values());
}

使用注意事项 #

当你用JdbcTemplate调用这个RowMapper时，返回的列表会包含重复的TruckPetrolStatsDTO对象（每一条订单对应一个），所以最后要从缓存Map中取出values，得到去重后的、每个卡车对应一个带订单列表的DTO。

内容的提问来源于stack exchange，提问作者valik