首页 > 代码库 > MySQL时间段查询,无数据补0

MySQL时间段查询,无数据补0

上一节提到分时间段统计,可是无数据的时候不显示,而此时我们需要让他显示0。

 

首先我们需要建一个时间表。

CREATE TABLE `my_date` (
  `date` date NOT NULL,
  PRIMARY KEY (`date`)
)

并且插入一些数据,具体怎么插入有好多种方法,目的是生成我们需要的时间格式,如下

技术分享

 

以下就要用到我们的时间表,与我们的数据表进行连接查询了。呃,中间的1你们可以忽略啊!!你们需要看的只有 t 和 count

mysql> select DATE_FORMAT(md.date,%Y-%m) t,1,count(*) from my_date md left join article a on DATE_FORMAT(md.date,%Y-%m) = DATE_FORMAT(a.article_time,%Y-%m) group by t;

+---------+---+----------+
| t       | 1 | count(*) |
+---------+---+----------+
| 2015-01 | 1 |        1 |
| 2015-02 | 1 |        1 |
| 2015-03 | 1 |        1 |
| 2015-04 | 1 |        1 |
| 2015-05 | 1 |        1 |
| 2015-06 | 1 |        1 |
| 2015-07 | 1 |        1 |
| 2015-08 | 1 |        1 |
| 2015-09 | 1 |        1 |
| 2015-10 | 1 |        1 |
| 2015-11 | 1 |        1 |
| 2015-12 | 1 |        1 |
| 2016-01 | 1 |        1 |
| 2016-02 | 1 |        1 |
| 2016-03 | 1 |        1 |
| 2016-04 | 1 |        1 |
| 2016-05 | 1 |        1 |
| 2016-06 | 1 |        1 |
| 2016-07 | 1 |        1 |
| 2016-08 | 1 |       22 |
| 2016-09 | 1 |       26 |
| 2016-10 | 1 |        3 |
| 2016-11 | 1 |        1 |
| 2016-12 | 1 |        1 |
+---------+---+----------+
24 rows in set

这里出现了一个问题,无数据的字段变成1了,哈哈哈,什么鬼呀,神经病啊。注意,这里的count(*) 代表统计所有的行,你数据表的那一行无数据,可是我们进行left join 获得的一行不止数据表吧?还有时间表的,时间表那里不为空,是有数据的,所以变成了1。所以我们要把count(*) 变成 count(a.article_id)

mysql> select DATE_FORMAT(md.date,%Y-%m) t,1,count(a.article_id) from my_date md left join article a on DATE_FORMAT(md.date,%Y-%m) = DATE_FORMAT(a.article_time,%Y-%m) group by t;
+---------+---+---------------------+
| t       | 1 | count(a.article_id) |
+---------+---+---------------------+
| 2015-01 | 1 |                   0 |
| 2015-02 | 1 |                   0 |
| 2015-03 | 1 |                   0 |
| 2015-04 | 1 |                   0 |
| 2015-05 | 1 |                   0 |
| 2015-06 | 1 |                   0 |
| 2015-07 | 1 |                   0 |
| 2015-08 | 1 |                   0 |
| 2015-09 | 1 |                   0 |
| 2015-10 | 1 |                   1 |
| 2015-11 | 1 |                   0 |
| 2015-12 | 1 |                   0 |
| 2016-01 | 1 |                   0 |
| 2016-02 | 1 |                   0 |
| 2016-03 | 1 |                   0 |
| 2016-04 | 1 |                   0 |
| 2016-05 | 1 |                   0 |
| 2016-06 | 1 |                   0 |
| 2016-07 | 1 |                   1 |
| 2016-08 | 1 |                  22 |
| 2016-09 | 1 |                  26 |
| 2016-10 | 1 |                   3 |
| 2016-11 | 1 |                   1 |
| 2016-12 | 1 |                   1 |
+---------+---+---------------------+
24 rows in set

到这里我们发现,时间表里面有多少时间就查出多少,如果我时间表里面有1亿个那还了得!所以我们要进行限制,比如要查当前年份的每个月数据。

mysql> select DATE_FORMAT(md.date,%Y-%m) t,1,count(a.article_id) from my_date md left join article a on DATE_FORMAT(md.date,%Y-%m) = DATE_FORMAT(a.article_time,%Y-%m) where DATE_FORMAT(md.date,%Y) = DATE_FORMAT(curdate(),%Y) group by t;
+---------+---+---------------------+
| t       | 1 | count(a.article_id) |
+---------+---+---------------------+
| 2016-01 | 1 |                   0 |
| 2016-02 | 1 |                   0 |
| 2016-03 | 1 |                   0 |
| 2016-04 | 1 |                   0 |
| 2016-05 | 1 |                   0 |
| 2016-06 | 1 |                   0 |
| 2016-07 | 1 |                   1 |
| 2016-08 | 1 |                  22 |
| 2016-09 | 1 |                  26 |
| 2016-10 | 1 |                   3 |
| 2016-11 | 1 |                   1 |
| 2016-12 | 1 |                   1 |
+---------+---+---------------------+
12 rows in set

如果我们要指定年份呢

mysql> select DATE_FORMAT(md.date,%Y-%m) t,1,count(a.article_id) from my_date md left join article a on DATE_FORMAT(md.date,%Y-%m) = DATE_FORMAT(a.article_time,%Y-%m) where DATE_FORMAT(md.date,%Y) = 2015 group by t;
+---------+---+---------------------+
| t       | 1 | count(a.article_id) |
+---------+---+---------------------+
| 2015-01 | 1 |                   0 |
| 2015-02 | 1 |                   0 |
| 2015-03 | 1 |                   0 |
| 2015-04 | 1 |                   0 |
| 2015-05 | 1 |                   0 |
| 2015-06 | 1 |                   0 |
| 2015-07 | 1 |                   0 |
| 2015-08 | 1 |                   0 |
| 2015-09 | 1 |                   0 |
| 2015-10 | 1 |                   1 |
| 2015-11 | 1 |                   0 |
| 2015-12 | 1 |                   0 |
+---------+---+---------------------+
12 rows in set

 

那个时间表里面的数据一定要事先生成咯,生成个几十年就够用了,公司说不定活不到那个时候呢哈哈哈,好好笑

下面进行一下每年的统计,到这里我时间表加入了2014年的12个月

mysql> select DATE_FORMAT(md.date,%Y) t,1,count(a.article_id) from my_date md left join article a on DATE_FORMAT(md.date,%Y-%m) = DATE_FORMAT(a.article_time,%Y-%m) group by t;
+------+---+---------------------+
| t    | 1 | count(a.article_id) |
+------+---+---------------------+
| 2014 | 1 |                   0 |
| 2015 | 1 |                   1 |
| 2016 | 1 |                  54 |
+------+---+---------------------+
3 rows in set

这个呢,时间表里面有多少年就显示多少年,我们也可以指定、

mysql> select DATE_FORMAT(md.date,%Y) t,1,count(a.article_id) from my_date md left join article a on DATE_FORMAT(md.date,%Y-%m) = DATE_FORMAT(a.article_time,%Y-%m) where DATE_FORMAT(md.date,%Y) > 2014 group by t;
+------+---+---------------------+
| t    | 1 | count(a.article_id) |
+------+---+---------------------+
| 2015 | 1 |                   1 |
| 2016 | 1 |                  54 |
+------+---+---------------------+
2 rows in set
mysql> select DATE_FORMAT(md.date,%Y) t,1,count(a.article_id) from my_date md left join article a on DATE_FORMAT(md.date,%Y-%m) = DATE_FORMAT(a.article_time,%Y-%m) where DATE_FORMAT(md.date,%Y) > 2014 and DATE_FORMAT(md.date,%Y) < 2016 group by t;
+------+---+---------------------+
| t    | 1 | count(a.article_id) |
+------+---+---------------------+
| 2015 | 1 |                   1 |
+------+---+---------------------+
1 row in set

 

MySQL时间段查询,无数据补0