MYSQL查询近一年 近一月 近一周 今天数据 没有数据返回0 按时间有序返回数据

select count(0) total,date_format(gmt_created,'%Y%m') time
        from application_main
        where is_del!=2
        and DATE_ADD(NOW(),INTERVAL(-12) MONTH) <= gmt_created 
        group by date_format(gmt_created,'%Y%m')
        order by date_format(gmt_created,'%Y%m') desc

/**
     * 获取近一年 年月时间
     */
    public static List<ApplicationMainDto> getInitMonthMapWithZero2(Integer num) {
        List<ApplicationMainDto> list = new ArrayList<>();
        Calendar c = Calendar.getInstance();
        for (int i = 0; i < num; i++) {
            int k = c.get(Calendar.YEAR);
            int j = c.get(Calendar.MONTH) + 1 - i;
            String date = "";
            if (j >= 1) {
                date = k + (j >= 10 ? "" : "0") + j;
            } else {
                int p = 11 - i;//剩余循环次数
                int m = c.get(Calendar.YEAR) - 1;
                int n = c.get(Calendar.MONTH) + 2 + p;
                date = m + (n >= 10 ? "" : "0") + n;
            }
            ApplicationMainDto applicationMainDto = new ApplicationMainDto();
            applicationMainDto.setTime(date);
            applicationMainDto.setTotal(0.0);
            list.add(applicationMainDto);
        }
        return list;
    }

{
"type":"success",
"data":[
{
"total":23.0,
"count":0.0,
"time":"202104"
},
{
"total":0.0,
"count":0.0,
"time":"202103"
},
{
"total":0.0,
"count":0.0,
"time":"202102"
},
{
"total":0.0,
"count":0.0,
"time":"202101"
},
{
"total":0.0,
"count":0.0,
"time":"202012"
},
{
"total":0.0,
"count":0.0,
"time":"202011"
},
{
"total":0.0,
"count":0.0,
"time":"202010"
},
{
"total":0.0,
"count":0.0,
"time":"202009"
},
{
"total":0.0,
"count":0.0,
"time":"202008"
},
{
"total":0.0,
"count":0.0,
"time":"202007"
},
{
"total":0.0,
"count":0.0,
"time":"202006"
},
{
"total":0.0,
"count":0.0,
"time":"202005"
}
],
"code":null,
"msg":null

SELECT
        date_add( curdate(), INTERVAL ( cast( help_topic_id AS signed INTEGER ) - 30 ) DAY ) time,
        ifnull( am.count, 0 ) total
        FROM
        mysql.help_topic h
        LEFT JOIN (
        SELECT
        date_format( gmt_created, '%Y-%m-%d' ) time,
        count( 0 ) count
        FROM
        application_main
        WHERE
        is_del = 1
        GROUP BY
        date_format( gmt_created, '%Y-%m-%d' )
        ) am ON date_add( curdate(), INTERVAL ( cast( h.help_topic_id AS signed INTEGER ) - 30 ) DAY ) = am.time
        WHERE
        h.help_topic_id <= DAY (
        last_day(
        curdate()))
        ORDER BY
        h.help_topic_id

SELECT
        date_add( curdate(), INTERVAL ( cast( help_topic_id AS signed INTEGER ) - 6 ) DAY ) time,
        ifnull( am.count, 0 ) total
        FROM
        mysql.help_topic h
        LEFT JOIN (
        SELECT
        date_format( gmt_created, '%Y-%m-%d' ) time,
        count( 0 ) count
        FROM
        application_main
        WHERE
        is_del = 1
        GROUP BY
        date_format( gmt_created, '%Y-%m-%d' )
        ) am ON date_add( curdate(), INTERVAL ( cast( h.help_topic_id AS signed INTEGER ) - 6 ) DAY ) = am.time
        WHERE
        h.help_topic_id <= 6
        ORDER BY
        h.help_topic_id

SELECT
  a.HOUR time,
  ifnull( b.count, 0 ) total
FROM
  (
  SELECT
    0 HOUR UNION ALL
  SELECT
    01 HOUR UNION ALL
  SELECT
    02 HOUR UNION ALL
  SELECT
    03 HOUR UNION ALL
  SELECT
    04 HOUR UNION ALL
  SELECT
    05 HOUR UNION ALL
  SELECT
    06 HOUR UNION ALL
  SELECT
    07 HOUR UNION ALL
  SELECT
    08 HOUR UNION ALL
  SELECT
    09 HOUR UNION ALL
  SELECT
    10 HOUR UNION ALL
  SELECT
    11 HOUR UNION ALL
  SELECT
    12 HOUR UNION ALL
  SELECT
    13 HOUR UNION ALL
  SELECT
    14 HOUR UNION ALL
  SELECT
    15 HOUR UNION ALL
  SELECT
    16 HOUR UNION ALL
  SELECT
    17 HOUR UNION ALL
  SELECT
    18 HOUR UNION ALL
  SELECT
    19 HOUR UNION ALL
  SELECT
    20 HOUR UNION ALL
  SELECT
    21 HOUR UNION ALL
  SELECT
    22 HOUR UNION ALL
  SELECT
    23 HOUR
  ) a
  LEFT JOIN (
  SELECT
    date_format( gmt_created, '%H' ) HOUR,
    count( 0 ) count
  FROM
    application_main
  WHERE
    is_del = 1
    AND date_format( gmt_created, "%Y-%m-%d" ) = date_format( now(), "%Y-%m-%d" )
  GROUP BY
    date_format( gmt_created, '%H' )
  ) b ON a.HOUR = b.HOUR
ORDER BY
  time