mysql处理前端展示需要的12月数据，没有数据的月份显示0

心已赠人 2023-08-17 16:30 102阅读 0赞

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

例如 出参List<MonthDataVO>
    
    MonthDataVO属性
    private String month;
    private Integer value;

需求是库中没有的月份，现实月份，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

<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>

实现2  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDE5NzQ5NA_size_16_color_FFFFFF_t_70]

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

pg库中可以使用  
generate-series

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDE5NzQ5NA_size_16_color_FFFFFF_t_70]: /images/20230810/5a0a52130e08438f80993cfcac4db67d.png