java写法(返回给前端用于eachers)
public Map<String, List> statisticsBook() {
Map<String, List> map = new HashMap<>();
Set list1 = new HashSet<>();
List result = new ArrayList<>();
List list2 = new ArrayList<>();
List<Map<String,String>> list= bizStandingBookMapper.statisticsBook();
if(list.size()>0){
list1 = list.get(0).keySet();
result = new ArrayList<>(list1);
Collection values = list.get(0).values();
list2 = new ArrayList(Collections.singleton(values.toString()));
}
map.put(“name”,result);
map.put(“count”,list2);
return map;
}
}
sql 写法,
SELECT
ifnull(sum(case DATE_FORMAT(amount_time,’%m’) when ‘01’ then amount_paid else 0 end),0) as 1月,
ifnull(sum(case DATE_FORMAT(amount_time,’%m’) when ‘02’ then amount_paid else 0 end),0) as 2月,
ifnull(sum(case DATE_FORMAT(amount_time,’%m’) when ‘03’ then amount_paid else 0 end),0) as 3月,
ifnull(sum(case DATE_FORMAT(amount_time,’%m’) when ‘04’ then amount_paid else 0 end),0) as 4月,
ifnull(sum(case DATE_FORMAT(amount_time,’%m’) when ‘05’ then amount_paid else 0 end),0) as 5月,
ifnull(sum(case DATE_FORMAT(amount_time,’%m’) when ‘06’ then amount_paid else 0 end),0) as 6月,
ifnull(sum(case DATE_FORMAT(amount_time,’%m’) when ‘07’ then amount_paid else 0 end),0) as 7月,
ifnull(sum(case DATE_FORMAT(amount_time,’%m’) when ‘08’ then amount_paid else 0 end),0) as 8月,
ifnull(sum(case DATE_FORMAT(amount_time,’%m’) when ‘09’ then amount_paid else 0 end),0) as 9月,
ifnull(sum(case DATE_FORMAT(amount_time,’%m’) when ‘10’ then amount_paid else 0 end),0) as 10月,
ifnull(sum(case DATE_FORMAT(amount_time,’%m’) when ‘11’ then amount_paid else 0 end),0) as 11月,
ifnull(sum(case DATE_FORMAT(amount_time,’%m’) when ‘12’ then amount_paid else 0 end),0) as 12月
FROM biz_standing_book
WHERE amount_time like concat(’%’,DATE_FORMAT( NOW( ), ‘%Y’ ),’%’)