mysql处理前端展示需要的12月数据，没有数据的月份显示0_数组要查出12个月的数据，没有的月份值为零

因为前端页面展示的趋势折线图需要12月份的数据，而数据库中只有个别月份的数据，就会返回一个对象list，里面只有有数据的月份。

例如 出参List<MonthDataVO>

MonthDataVO属性
private String month;
private Integer value;
1
2
3
4
5

需求是库中没有的月份，现实月份，value=0

原来的s q l

    <select id="getMonthData" resultType="com.jp.demo.pojo.vo.MonthDataVO">
        select month as month ,
        sum(saleNum) as value
        from phone_sale_info
        group by month
    </select>
1
2
3
4
5
6

实现1

   <select id="getMonthData" resultType="com.jp.demo.pojo.vo.MonthDataVO">
        select b.month ,IFNULLifnull(a.value,0) from 
        (select month as month ,
        sum(saleNum) as value
        from phone_sale_info
        group by month)a
        right join 
        (select '1' as month from dual union all 
        select '2' as month from dual union all 
        select '3' as month from dual union all 
        select '4' as month from dual union all 
        select '5' as month from dual union all 
        select '6' as month from dual union all 
        select '7' as month from dual union all 
        select '8' as month from dual union all 
        select '9' as month from dual union all 
        select '10' as month from dual union all 
        select '11' as month from dual union all 
        select '12' as month from dual ) b on a.month=b.month
    </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

实现2

 select b.everyMonth ,ifnull(a.value,0)
        from (
        select month as month ,
        sum(saleNum) as value
        from phone_sale_info
        group by month)a
        right join
        (select @month := @month +1 everyMonth
        from phone_sale_info,(select @month :=0) aa limit 12) b
        on a.month=b.everyMonth
1
2
3
4
5
6
7
8
9
10

pg库中可以使用
generate-series