MYSQL查询最近一年、最近一月、最近一周，当天的数据， 没有数据就返回0， 按时间有序返回数据

写在前面

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

此sql运行后会返回近一年有数据的月份 需要配合工具类进行使用 工具类如下

 /**
     * 获取近一年 年月时间
     */
    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

返回结果:

这篇好文章是转载于：学新通技术网