PostgreSQL报表统计历史12月度数据，无数据补零

• SQL：统计历史12个月数据，无数据补0

select a.repair_date,
       COALESCE(b.count, 0) as count
  from(
SELECT TO_CHAR(NOW(), 'YYYY-MM') as repair_date
 union all
SELECT TO_CHAR(NOW() - INTERVAL '1 MONTH', 'YYYY-MM') as repair_date
 union all
SELECT TO_CHAR(NOW() - INTERVAL '2 MONTH', 'YYYY-MM') as repair_date
 union all
SELECT TO_CHAR(NOW() - INTERVAL '3 MONTH', 'YYYY-MM') as repair_date
 union all
SELECT TO_CHAR(NOW() - INTERVAL '4 MONTH', 'YYYY-MM') as repair_date
 union all
SELECT TO_CHAR(NOW() - INTERVAL '5 MONTH', 'YYYY-MM') as repair_date
 union all
SELECT TO_CHAR(NOW() - INTERVAL '6 MONTH', 'YYYY-MM') as repair_date
 union all
SELECT TO_CHAR(NOW() - INTERVAL '7 MONTH', 'YYYY-MM') as repair_date
 union all
SELECT TO_CHAR(NOW() - INTERVAL '8 MONTH', 'YYYY-MM') as repair_date
 union all
SELECT TO_CHAR(NOW() - INTERVAL '9 MONTH', 'YYYY-MM') as repair_date
 union all
SELECT TO_CHAR(NOW() - INTERVAL '10 MONTH', 'YYYY-MM') as repair_date
 union all
SELECT TO_CHAR(NOW() - INTERVAL '11 MONTH', 'YYYY-MM') as repair_date) a
  left join(
select TO_CHAR(repair_time, 'YYYY-MM') as datetime, count(id) as count
  from eoms.obj_dtu_repair
 group by TO_CHAR(repair_time, 'YYYY-MM')) b on a.repair_date= b.datetime
 order by a.repair_date;

• JPA Repository 接口查询

@Query(value = "上述SQL...",nativeQuery = true)
    List<Object[]> dtuRepairCount();

• Controller 解析

List<Object[]> list = objDtuRepairRepository.dtuRepairCount();
List<RepairReportModel> repairReportModels = new ArrayList<>();

for (int i = 0; i < list.size(); i++) {
    Object[] col = list.get(i);
    RepairReportModel repairReportModel = new RepairReportModel();

    repairReportModel.setRepairDate((String) col[0]);
    repairReportModel.setCount((BigInteger) col[1]);

    repairReportModels.add(repairReportModel);
}