mysql与oracle按月分组查询统计数量小结

mysql：

SELECT
    DATE_FORMAT( create_time, '%m' ) AS monthName,
    sum( 1 ) AS monthCount
FROM
    `rs_complain`
WHERE
    is_deleted = 0
GROUP BY
    monthName

oracle:

select to_char(t.AAA008, 'MM') as MONDATE, sum(1) as MONCOUNT
from ${db1}ab02 t
where to_char(t.AAA008, 'yyyy') = #{year}
GROUP BY to_char(t.AAA008, 'MM')
ORDER BY MONDATE


后台处理为12个月展示

List<HomePageDto> newAddListResult = getResult(newAddList);

private List<HomePageDto> getResult(List<HomePageDto> newAddList) {
    List<HomePageDto> newList = new ArrayList<>();
    for (int i = 1; i < 13; i ++){
        HomePageDto homePageDto = new HomePageDto();
        homePageDto.setMonthCount(0);
        homePageDto.setMonthName(i+"月");
        newList.add(homePageDto);
    }
    List<HomePageDto> newList1 = new ArrayList<>();
    for (int i = 0; i < newAddList.size(); i++){
        HomePageDto homePageDto1 = newAddList.get(i);
        HomePageDto homePageDto = new HomePageDto();
        String monthName = homePageDto1.getMonthName();
        if(Integer.parseInt(monthName) < 10){
            String mon = monthName.substring(1);
            homePageDto.setMonthName(mon + "月");
            homePageDto.setMonthCount(homePageDto1.getMonthCount());
            newList1.add(homePageDto);
        }else{
            homePageDto.setMonthName(homePageDto1.getMonthName() + "月");
            homePageDto.setMonthCount(homePageDto1.getMonthCount());
            newList1.add(homePageDto);
        }
    }
    newList.forEach(m -> {
        newList1.forEach(p -> {
            if(p.getMonthName().equals(m.getMonthName())){
                m.setMonthCount(p.getMonthCount());
            }
        });
    });
    return newList;
}