本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议 》和 《 阿里云开发者社区知识产权保护指引 》。如果您发现本社区中有涉嫌抄袭的内容,填写 侵权投诉表单 进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

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


业务层调用


20210430161254382.png


返回结果:


{
"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
        mysql.help_topic h
        LEFT JOIN (
        SELECT
        date_format( gmt_created, '%Y-%m-%d' ) time,
        count( 0 ) count
        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


返回结果:


20210430161950741.png


近一周


SELECT
        date_add( curdate(), INTERVAL ( cast( help_topic_id AS signed INTEGER ) - 6 ) DAY ) time,
        ifnull( am.count, 0 ) total
        mysql.help_topic h
        LEFT JOIN (
        SELECT
        date_format( gmt_created, '%Y-%m-%d' ) time,
        count( 0 ) count
        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
    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
    LEFT JOIN (
    SELECT
        date_format( gmt_created, '%H' ) HOUR,
        count( 0 ) count
        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


返回结果:


20210430162030472.png

AnalyticDB MySQL游戏行业数据分析实践 函数计算X RDS PostgreSQL,基于LLM大语言模型构建AI知识库 AnalyticDB MySQL海量数据秒级分析体验 5分钟上手Flink MySQL连接器 PolarDB for MySQL 多主集群体验 基于ECS+RDS的在线留言系统