Mysql实现某一年1到12月的统计查询

一.创建一个视图

CREATE 
VIEW `v_12_month` AS
select date_format('2021-01-01','%m') AS `month` 
union select date_format(('2021-01-01' - interval 1 month),'%m') AS `month`
union select date_format(('2021-01-01' - interval 2 month),'%m') AS `month` 
union select date_format(('2021-01-01' - interval 3 month),'%m') AS `month` 
union select date_format(('2021-01-01' - interval 4 month),'%m') AS `month` 
union select date_format(('2021-01-01' - interval 5 month),'%m') AS `month`
union select date_format(('2021-01-01' - interval 6 month),'%m') AS `month`
union select date_format(('2021-01-01' - interval 7 month),'%m') AS `month` 
union select date_format(('2021-01-01' - interval 8 month),'%m') AS `month` 
union select date_format(('2021-01-01' - interval 9 month),'%m') AS `month` 
union select date_format(('2021-01-01' - interval 10 month),'%m') AS `month`
union select date_format(('2021-01-01' - interval 11 month),'%m') AS `month`

说明:验证视图

select v_12_month ORDER BY MONTH ASC

返回结果 

month
01
02
03
04
05
06
07
08
09
10
11
12

ok,没有问题,走下一步

 二.创建一个Mapper.xml

    <select id="findApplyApproval" parameterType="java.lang.String" resultType="com.zysoft.whstandard.entity.vo.ApplyApprovalVo">
        select v.month,ifnull(a.aSum,0) total,ifnull(b.bSum,0) projectSum,ifnull(c.cSum,0) approvalSum from v_12_month v
left join
          (select DATE_FORMAT(t.create_time,'%m') month,count(t.id) aSum
           from t_project_application t where DATE_FORMAT(t.create_time,'%Y-%m')> DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m')
           and t.logic_delete=0
        <if test="_parameter !=null and _parameter !=''">
            and year(t.create_time) =#{_parameter}
        </if>
           group by  month)a on v.month = a.month
left join
          (select DATE_FORMAT(t.create_time,'%m') month,count(t.id) bSum
           from t_project_application t where DATE_FORMAT(t.create_time,'%Y-%m')> DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m')
           and t.logic_delete=0
           and t.audit_status=1
        <if test="_parameter !=null and _parameter !=''">
            and year(t.create_time) =#{_parameter}
        </if>
        group by  month)b on v.month = b.month
left join
          (select DATE_FORMAT(t.create_time,'%m') month,count(t.id) cSum
           from t_standard_approval t where DATE_FORMAT(t.create_time,'%Y-%m')> DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m')
           and t.logic_delete=0
           and t.audit_status=1
        <if test="_parameter !=null and _parameter !=''">
            and year(t.create_time) =#{_parameter}
        </if>
        group by  month)c on v.month = c.month
group by v.month order by v.month asc
    </select>

 三.创建一个Mapper接口

  /**
     * 首页申请审核数量统计,注意数据库需要先创建视图
     * @param year
     * @return
     */
    List<ApplyApprovalVo> findApplyApproval(String year);

 三.Postman测试接口

1.测试地址:localhost:18196/statistical/findApplyApproval?year=2020

2.返回结果如下:

{
    "msg": "success",
    "code": 200,
    "data": [
        {
            "total": 0,
            "projectSum": 0,
            "approvalSum": 0,
            "month": "01"
        },
        {
            "total": 0,
            "projectSum": 0,
            "approvalSum": 0,
            "month": "02"
        },
        {
            "total": 0,
            "projectSum": 0,
            "approvalSum": 0,
            "month": "03"
        },
        {
            "total": 0,
            "projectSum": 0,
            "approvalSum": 0,
            "month": "04"
        },
        {
            "total": 0,
            "projectSum": 0,
            "approvalSum": 0,
            "month": "05"
        },
        {
            "total": 0,
            "projectSum": 0,
            "approvalSum": 0,
            "month": "06"
        },
        {
            "total": 0,
            "projectSum": 0,
            "approvalSum": 0,
            "month": "07"
        },
        {
            "total": 0,
            "projectSum": 0,
            "approvalSum": 0,
            "month": "08"
        },
        {
            "total": 0,
            "projectSum": 0,
            "approvalSum": 0,
            "month": "09"
        },
        {
            "total": 0,
            "projectSum": 0,
            "approvalSum": 0,
            "month": "10"
        },
        {
            "total": 0,
            "projectSum": 0,
            "approvalSum": 0,
            "month": "11"
        },
        {
            "total": 3,
            "projectSum": 0,
            "approvalSum": 0,
            "month": "12"
        }
    ]
}