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