MySql查询时日期补全(连续)的一种方法

原创 2017年03月11日 17:25:29

MySql查询时日期补全(连续)的一种方法

某一日,需要查询订单表中前7天每天的订单数,sql依次写出来:

SELECT
    DATE_FORMAT(order_time,'%Y-%m-%d') date,
    count(*) orderCount
FROM
    order
WHERE
    date_sub(curdate(), INTERVAL 7 DAY) < date(order_time)
GROUP BY DATE_FORMAT(order_time,'%Y-%m-%d')
ORDER BY date ASC

一执行,数据出来了,不过不连续,数量也没有7条,老板生意惨淡,很多时候一天不能来一个单,但程序不能没有数据,所以这里直观的办法就是补上差的数据,怎么补?
第一想到的就是用上层语言来补,比如java:

//从数据库中查询指定天数之前到当前每天的订单数
List<Map<String, Object>> orders= orderDao.queryOrder(7);
//补全数据库中不存在的日期,订单数为0
List<String> dayList = DateUtil.dayList(7, 1,false);//得到7天前到今天每天的日期
boolean exists=false;
for (int i=0;i<dayList.size();i++) {
    exists=false;
    for (Map<String, Object> hs : orders) {
        if (dayList.get(i).equals(hs.get("date"))) {
            exists=true;
            break;
        }
    }
    if(!exists){
        final String date=dayList.get(i);
        orders.add(i,new HashMap<String,Object>(){{
            put("orderCount", 0);
            put("datat", date);
        }});
    }
}
return orders;

ok了,没问题。
转过头,老板说7天业绩看不出我店半年不开张开张吃半年的本质,要求要自定义开始结束时间来查,我说,好嘞您!
不过上面那种方法就存在问题了,真要选2年的日期来查,那得循环多少次啊?于是开动脑筋,由这里得到启发: mysql按日期分组(group by)查询统计的时候,没有数据补0的解决办法,下面来写sql:

SELECT
    od.date,
    od.orderCount
FROM
    (
        SELECT
            DATE_FORMAT(order_time,'%Y-%m-%d') date,
            count(*) orderCount
        FROM
            order
        WHERE
            order_time>'开始时间' and order_time<'结束时间'
        GROUP BY DATE_FORMAT(order_time,'%Y-%m-%d')

        UNION (SELECT 0, '2017-03-01')
        UNION (SELECT 0, '2017-03-02')
        UNION (SELECT 0, '2017-03-03')
        UNION (SELECT 0, '2017-03-04')
        UNION (SELECT 0, '2017-03-05')
        UNION (SELECT 0, '2017-03-06')
        UNION (SELECT 0, '2017-03-07')
        UNION (SELECT 0, '2017-03-08')
        -- 很多个UNION ......
    ) AS od
GROUP BY od.date
ORDER BY od.date ASC

妥了!至于sql中间那一长串的UNION语句,那就根据选择的日期用java生成吧!
估计还有更好的方法,欢迎赐教!

mysql查询日期补全(连续)方法

SELECT od.date, od.orderCount FROM ( SELECT DATE_FORMAT(order_time,'...
  • qd824692746
  • qd824692746
  • 2017年06月06日 18:15
  • 1224

mysql查询连续时间数据——无数据补0

用D3来画统计图,数据是从mysql数据库中查出来的。 统计图是关于某段时间内,每年/每月/每周的微博数量的折线图。数据表是一条微博为一条记录,每条记录有日期这个字段。 首先考虑用group by来统...
  • u012603073
  • u012603073
  • 2016年03月03日 11:38
  • 9893

MySQL中实现连续日期内数据统计,缺省天数0补全

MySQL中实现连续日期内数据统计,缺省天数0补全昨天群里有同学在问:想要查找当前日期到前7天时间内,每天的数据量总和是多少?这个相信很多人都可以想到直接用group by data就可以实现,但是这...
  • WxQ92222
  • WxQ92222
  • 2017年12月22日 11:05
  • 161

mysql 按月统计数据 没有数据按0补全

也借鉴了网上的做法,但不全相同,这里补全。 CREATE TABLE num (i int);-- 创建一个表用来储存0-9的数字 INSERT INTO num (i) VALUES (0)...
  • yuke98727
  • yuke98727
  • 2017年02月09日 18:03
  • 1703

MSSQL如何实现日期的自动补全(补全数据表中缺失的日期)

在实际生活中我们会遇到很多日期缺失的问题,但在统计的时候要求 显示出缺失的日期,面对这样的问题,我们如何实现?这里我把自己 学习的三种方法给大家分享一下: 方法一:创建存储过程实现 ...
  • TravyLee
  • TravyLee
  • 2012年03月09日 12:50
  • 5662

mysql 缺省日期列

经常有这种需求 某日期列要缺省保持最新时间: create table testtimestamp1(u varchar(100),updatetime timestamp NULL DEFA...
  • xuanxuan_good
  • xuanxuan_good
  • 2016年12月15日 17:02
  • 313

R语言 对日期数据的缺失补全方法

  • 2017年08月30日 19:16
  • 923B
  • 下载

Mysql如何查询连续时间内的次数

转自:http://www.tuicool.com/articles/uyEZJf 在网上看到一道有意思的题目,大意是如何在mysql查询连续在线的天数。 所谓的连续在线是指相邻两天都登录过,不...
  • Manketon
  • Manketon
  • 2015年02月03日 20:48
  • 4812

mysql循环日期

循环日期 CREATE DEFINER=`XX`@`192.168.22.%` PROCEDURE `loop_date`() BEGIN declare i date; #定义i变量 d...
  • litter_Strong
  • litter_Strong
  • 2017年01月10日 14:03
  • 1131

在mysql中根据日期来统计出用户行为属性的连续天数

SELECT days     FROM (     SELECT user_id, max(days) days, min(login_day) start_date,max(login_day...
  • yxd179
  • yxd179
  • 2017年08月28日 16:07
  • 238
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySql查询时日期补全(连续)的一种方法
举报原因:
原因补充:

(最多只允许输入30个字)