解决mysql按照日期分组查询中不存在的日期需要补充为0的统计问题

先贴出测试用的建表语句

CREATE TABLE `yx_order` (
  `id` varchar(36) NOT NULL COMMENT '订单ID',
  `money` varchar(36) DEFAULT NULL COMMENT '金额',
  `memberId` varchar(36) DEFAULT NULL COMMENT '活动会员ID',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  `updateTime` datetime DEFAULT NULL COMMENT '修改时间',
  `state` varchar(255) DEFAULT '0' COMMENT '0 未支付 1 已支付 2 已领取 3 已过期 ',
  `commodityId` varchar(36) DEFAULT NULL COMMENT '商品ID',
  `number` int(11) DEFAULT NULL COMMENT '商品数量',
  `addressId` varchar(36) DEFAULT NULL COMMENT '订单id',
  `appId` varchar(36) DEFAULT NULL COMMENT 'appId',
  PRIMARY KEY (`id`),
  KEY `memberIdInd` (`memberId`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动订单表';

由于公司的项目需要使用到echarts数据可视化,需要对订单表进行一个从2个日期之间查出每天的订单量。如果只考虑到字面上的理解,那么select * from yx_order group by createTime 加上between and就可以了。但是,如果连续几天都没有订单生产,并且正好筛选到了这几天,那么前台页面的图形表就死掉了。一种解决办法就是在数据库查询的时候把不连续的日期查询补充为连续的日期查询。

在这里顺便复习了mysql的几种查询,内连接inner join相当于2个结果集的交集,left join则是左边结果集加上交集,union联合查询则是进行并集查询。我们可以先进行一个在指定时间范围的数据为0的查询,然后和需要的查询结果集union all起来,就得到了需要的数据。这里需要注意的是,2个结果集的交集,会使用第一个结果集的交集,顺序不要写错。

SELECT date,sum from (select  DATE_FORMAT(createTime,'%Y-%m-%d') date,SUM(money) sum from yx_order where DATE_FORMAT(createTime,'%Y-%m-%d')
BETWEEN DATE_FORMAT('2018-10-25','%Y-%m-%d') and DATE_FORMAT(DATE_ADD('2018-11-04',INTERVAL 1 day),'%Y-%m-%d') 
GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d') UNION all 
SELECT
    @cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ) date,
    0 AS sum
FROM
    ( SELECT @cdate := DATE_ADD('2018-11-05', INTERVAL + 1 DAY ) FROM yx_order ) as t1 
WHERE
    @cdate > '2018-10-25'   ) as t2 GROUP BY date asc

 

  • 2
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值