Mysql按日期分组(group by)查询统计的时候,没有数据补0的解决办法

时间:2021-08-10 23:46:06

转载自:http://blog.csdn.net/jie11447416/article/details/50887888

1、案例中的数据结构和数据如下

Mysql按日期分组(group by)查询统计的时候,没有数据补0的解决办法

2、在没有解决的时候,是这样的

  SELECT date(downtime) AS dday, count(*) AS num FROM re_device GROUP BY dday 

得到如下结果,如果那天没有数据,那麽就会没有记录 

Mysql按日期分组(group by)查询统计的时候,没有数据补0的解决办法

我们看到,时间不连续,没有2016-3-05这一天的,这样本来不是问题,但是,我拿出来的数据,还要画出图表呀,没有当然不行,我们需要的是下面这个样子的。

Mysql按日期分组(group by)查询统计的时候,没有数据补0的解决办法

简单的说就是,没有数据,就要补充一个0.

3、下面我们讲实现

我们要生成一个日历的表,然后和原来的数据,联合查询,说到这里,大家就知道很low了,但是,限于我水平有限,研究这个问题,半天,这个是我找到的比较好的一种实现方式。如果你又更好的,也请你给我说下。 
执行下面的sql,直接诶生成日历的表(calendar)

CREATE TABLE num (i int);-- 创建一个表用来储存0-9的数字
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);-- 生成0-9的数字,方便以后计算时间 CREATE TABLE if not exists calendar(datelist date); -- 生成一个存储日期的表,datalist是字段名 -- 这里是生成并插入日期数据
INSERT INTO calendar(datelist) SELECT
adddate(
( -- 这里的起始日期,你可以换成当前日期
DATE_FORMAT("2016-1-1", '%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;

这里我用了100000条记录,算出来到2289年了,完全够用了,到那个时候,出问题,我也管不了了。

完成之后,请删除num的零时表

4、联合查询

 SELECT
date(dday) ddate,
count(*) - 1 as num
FROM
(
SELECT
datelist as dday
FROM
calendar
-- 这里是限制返回最近30天的数据
where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(datelist)&&date(datelist)<=CURDATE()
UNION ALL
SELECT
downtime
FROM
re_device
) a
GROUP BY ddate

5、其他解决方法 
当然,应该还有其他的解决方案,但是博主就没有去写了,有时间可以去写一下。 
我用的spring mvc,所以,也还是可以在java代码中补充完整的,因为数据返回的是个map对象,那麽我们要遍历这个对象,直接用calendar对象,生成日期作为key来遍历,如果没有数据,就put进去一个0,然后在限制一下,需要多少天的,就可以了。数据就完整了。 
但是这样也有一个问题,那就是map里的数据顺序会有问题,所以,使用的时候,也必须是生成calendar对象,然后构造出来日期作为key来遍历。或者用Collections.sort()排序一下。

我个人觉得,还是上数据直接生成得比较好一点。

Mysql按日期分组(group by)查询统计的时候,没有数据补0的解决办法的更多相关文章

  1. MySQL按日期分组并统计截止当前时间的总数(实例教程)

    MySQL按日期分组并统计截止当前时间的总数 建表语句 SET NAMES utf8mb4; ; -- ---------------------------- -- Table structure ...

  2. HBase 高性能获取数据&lpar;多线程批量式解决办法&rpar; &plus; MySQL和HBase性能测试比较

    摘要:   在前篇博客里已经讲述了通过一个自定义 HBase Filter来获取数据的办法,在末尾指出此办法的性能是不能满足应用要求的,很显然对于如此成熟的HBase来说,高性能获取数据应该不是问题. ...

  3. mysql按日期分组统计数据

    最近在做一个招聘网时,需要显示一个月内企业招聘信息的发布数量,按日期分组统计,刚开始是直接从源数据库表里面进行group by,但这样子就出现日期不连续的问题了,我想要的效果是,若当天没有数据,则显示 ...

  4. Mysql 分组查询出现&&num;39&semi;this is incompatible with sql&lowbar;mode&equals;only&lowbar;full&lowbar;group&lowbar;by&&num;39&semi;的解决办法

    由于Mysql自动开启了 only_full_group_by,所以若查询的字段不在group by里面,则分组报错. 解决办法其一:mysql配置,关闭only_full_group_by,这种办法 ...

  5. 报表统计——java实现查询某年某月每天数据,没数据补0

    一般图表绘制例如echarts等,返回数据格式都大同小异.重点是利用sql或者java实现数据格式的转型,接下来是关键部分: 1.前提:提供的工具方法——获取某月有多少天 //通过年份和月份确定该月的 ...

  6. python3操作mysql数据库表01(封装查询单条、多条数据)

    #!/usr/bin/env python# -*- coding:UTF-8 -*- import pymysql# import os'''封装查询单条.多条数据'''# os.environ[' ...

  7. MySQL:Error &colon; Tablespace for table &&num;39&semi;&grave;database&grave;&period;&grave;temp&grave;&&num;39&semi; exists&period; Please DISCARD the tablespace before IMPORT&period;解决办法

    今天在navicat上操作mysql数据库表,突然没有响应了.随后重启,mysql服务也终止了.随后启动服务,检查表,发现一张表卡没了,就重新添加一张表.报了一个错: Error : Tablespa ...

  8. &lbrack;MySQL&rsqb;在安装windows版MySQL时遇到过如下问题Error Nr&period;1045和Error&period;Nr&period;2003,相应解决办法如下

    1.准备mysql server-5.0.27.exe 2.按照指导安装,在安装到最后一步时遇到如下两个错误: 2.1.出现错误Error Nr.1045 解决办法: a).停止MySQL服务:我的电 ...

  9. Navicat新建查询,系统找不到指定路径 独家解决办法

    Navicat新建查询系统找不到指定路径,很多人用了网上流行的那些解决办法,还是无法解决.比如: https://jingyan.baidu.com/article/86112f1387a713273 ...

随机推荐

  1. java创建线程的几种方式

    1.继承Thread类 /** * @author Ash * @date: 2016年8月6日 下午10:56:45 * @func: 通过继承Thread类来实现多线程 * @email 4086 ...

  2. addClass 函数

    javascript: function addClass(id,new_class){ var i,n=0; new_class=new_class.split(","); fo ...

  3. Oracle归档日志定时删除任务

    1.在Oracle账号下,创建归档日志删除文件del_arch.sh 文件位置:/home/oracle/crontabOra,内容如下: #!/bin/bash LOG_DIR=/home/orac ...

  4. H - Can you answer these queries&quest; - (区间查询更新)

    有一列数,(都是2^63范围内的并且都大于0的整数),现在呢有一些操作, 操作 0 可以把区间LR内的所有数都变成它的平方根数(是取整后的),操作 1 可以就是求区间LR内的和了. 分析:因为这个操作 ...

  5. sql server 2000 和 sql server 2005 数据库连接字符串区别

    //sql server 2000 <add name="Connection" connectionString="Data Source=.;Initial C ...

  6. 通过SecureCRT下载远程Linux服务器上的文件到本地Windows

    sz  文件名[先cd到需要下载的文件的目录层] [root@is13084905-0233 bookinterface]# sz test.txt rz Starting zmodem transf ...

  7. 11g默认审计选项

    [注:参考了maclean的网文]11g默认审计选项AUDIT_TRAIL参数的缺省值为DB,审计数据记录在数据库中的AUD$审计字典基表上.在11g中CREATE SESSION将被作为受审计的权限 ...

  8. MyEclipse中好用的快捷键汇总

    MyEclipse中常用的快捷键有很多,合理的使用其中一些快捷键组合,可以有效提高开发的效率和质量. 1.Ctrl + Shift + R:打开资源.可以查找并打开工作区中任何一个文件,且支持使用通配 ...

  9. JS 中 原生方法 &lpar;二&rpar; --- 数组 &lpar;修---添加ES6新增&rpar;

    const arr = [1, 2, 3, 5, 'a', 'b'] /** * * length * 这个只能被 称之为 数组的原生属性, 返回 一个 number * arr.length */ ...

  10. Dnsmasq 配置PXE批量安装系统

    以下测试都是基于centos 7下的环境 需要安装的软件为 nginx 用来下载ks.cfg和系统镜像文件用的,也可以用ftp服务器来代替 dnsmasq 提供dhcp服务和tftp服务,也可以单独去 ...