MySQL获取近30日数据，没有补零

最新推荐文章于 2025-04-19 23:01:14 发布

qq_36472439

最新推荐文章于 2025-04-19 23:01:14 发布

阅读量5.9k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： MySQL Java

本文链接：https://blog.youkuaiyun.com/qq_36472439/article/details/84135616

Java 同时被 2 个专栏收录

13 篇文章

订阅专栏

MySQL

1 篇文章

订阅专栏

在构建数据报表时，面对MySQL查询出的近30日数据可能存在缺失值的问题，本文介绍了两种解决方案：一是通过重写SQL语句结合IFNULL函数，二是后端代码处理，将缺少的数据补充为0。作者分享了自己的方法，包括创建临时calendar表进行联合查询，以及在Java代码中处理数据。寻求更优解的同时，也指出这两种方法的局限性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

当我们在做数据报表的时候，经常会去查询近一段时间显示的数据，但是拿到的数据却是某一天没数据的话是查询不出来的，但是我们希望没数据的那天显示为0

MySQL获取当前时间的函数

URDATE() ：2008-12-29

NOW()：2008-12-29 16:25:46

CURTIME()：16:25:46

SELECT DATE_FORMAT(user_app.create_time,'%Y-%m-%d') as create_time,
count(1) as total
    FROM user_app
    where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time)
    GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d')

我们会发现如果某一天没数据的话是不会显示为0的，这时有两种解决办法

其一：重写sql语句

其二：在返回数据的java代码中重新拼接数据，先获取三十日的日期，把没数据的补0即可，

我们先说第一种，

在有的博客中提到用如下办法解决

构建一个最近7天的结果集，没有的利用ifnull函数补0操作

select a.click_date,ifnull(b.count,0) as count
from (
    SELECT curdate() as click_date
    union all
    SELECT date_sub(curdate(), interval 1 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 2 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 3 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 4 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 5 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 6 day) as click_date
) a left join (
  select date(FROM_UNIXTIME(getTime/1000,'%Y%m%d')) as datetime, count(*) as count
  from userwechatcard
  group by date(FROM_UNIXTIME(getTime/1000,'%Y%m%d'))
) b on a.click_date = b.datetime;

但是如果是近30天，100天呢，语句是不会显得很繁琐，

我的解决方法是

1：再创建两个表

num表，后面删除即可

CREATE TABLE num (i INT);
INSERT INTO num (i) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

calendar表

CREATE TABLE  if not exists calendar(datelist date); -- 生成一个存储日期的表，datalist是字段名

-- 这里是生成并插入日期数据
INSERT INTO calendar(datelist) SELECT
    adddate(
        (   -- 这里的起始日期，你可以换成当前日期
            DATE_FORMAT("2018-11-14", '%Y-%m-%d') 
        ),
        numlist.id
    ) AS `date`
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
        CROSS JOIN num AS n100
        CROSS JOIN num AS n1000
        CROSS JOIN num AS n10000
    ) AS numlist;

calendar生成了100000条日期数据，有生之年完全够用，完成后删除num表

2：两个表联合查询

SELECT
    date(dday) ddate,
    count(*) - 1 as num
FROM
    (
        SELECT
            DATE_FORMAT(create_time,'%Y-%m-%d')as dday,device_name
        FROM
            calendar 
            where  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time)&&date(create_time)<=CURDATE() 
            UNION 
            SELECT
							DATE_FORMAT(create_time,'%Y-%m-%d') ,device_name
            FROM
							message_all
						where  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time)&&date(create_time)<=CURDATE()
    )a
GROUP BY ddate

结果如下

达到目的没有的也显示出来了

第二种解决方法：如果你是不能操作数据库的话，在返回的数据中拼接

java获取当前日期及其三十天前的日期放到一个集合中，把查询出来的数据和日期集合的数据进行处理，没有补0，

/**
	 * 近三十日
	 * @return
	 */
	@Override
	public List<Count> count() {
		try {
			List<Count> counts=userAppMapper.count();
			// 得到一个Calendar的实例
			Calendar begin = Calendar.getInstance();
			// 设置时间为当前时间
			begin.setTime(new Date());
			// 月份减1
			begin.add(Calendar.MONTH, -1);
			// 日期加1
			begin.add(Calendar.DATE, +1);
			Date result = begin.getTime();
			Calendar end = Calendar.getInstance();
			Long startTime = begin.getTimeInMillis();
			Long endTime = end.getTimeInMillis();
			// 一天的时间转化为ms
			Long oneDay = 1000 * 60 * 60 * 24L;
			List dates = new ArrayList<>();
			Long time = startTime;
			List<String> createTimeList=new ArrayList<>();
			for (int k=0;k<counts.size();k++){
				Count count=JSON.parseObject(JSON.toJSONString(counts.get(k)),Count.class);
				createTimeList.add(count.getCreateTime());
			}
			int i = 0;
			while (time <= endTime) {
				Date d = new Date(time);
				DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
				dates.add(i, df.format(d));
				i++;
				time += oneDay;
			}
			dates.removeAll(createTimeList);
			System.out.println("dates:"+dates);
			List<Count> countList=new ArrayList<>();
			for (int j = 0; j < dates.size(); j++) {
				Count countNow=new Count();
				System.err.println("dates:"+dates.get(j));
				countNow.setTotal(0);
				countNow.setCreateTime((String) dates.get(j));
				countList.add(countNow);
			}
			System.out.println("countList:"+countList);
			System.out.println("数据库counts:"+counts);
			for (int k=0;k<counts.size();k++){
				Count count=JSON.parseObject(JSON.toJSONString(counts.get(k)),Count.class);
				countList.add(count);
				System.out.println("count:"+count);
			}
			Collections.sort(countList, new Comparator<Count>() {
				@Override
				public int compare(Count o1, Count o2) {
					SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
					try {
						return (int) (simpleDateFormat.parse(o1.getCreateTime()).getTime()/1000-simpleDateFormat.parse(o2.getCreateTime()).getTime()/1000);
					} catch (ParseException e) {
						e.printStackTrace();
					}
					return 0;
				}
			});
			System.out.println(countList.size());
			return countList;
		}catch (Exception e) {
			throw new BusinessException(ResponseCode.RUN_TIME_EXCEPTION, e.getMessage());
		}
	}

我直接拿上一个月的日期数据，同样能达到预期效果，两种都不是我想要的，第一种多建了一个calendar表，第二种需要进行数据操作，如果您有更好的解决方法，还望不吝赐教，本人才疏学浅，文档多有不足（本就是个人学习之余的笔记），不喜勿喷