mysql 按天统计访问量，没有的补0

原文我的博客：mysql 按天统计访问量，没有的补0

思路：
先把时间格式成，然后按时间分组查询

SELECT
	DATE_FORMAT( created_at,'%Y-%m-%d') access_day,
	count( id ) num
FROM
	access_logs
GROUP BY
	access_day;
复制代码

执行验证一下

grom 中使用

上面的查询，只能查询出有记录的数据，如果某天没有数据，就会出现下面的现象：

而我们想要的是连续的：

网上查询了些资料，大概的思路是需要利用一张零时表，生成你需要的日期，然后再连表查询，这里我给出我的实际操作。

比如：我要搜素近7天的访问记录
首先利用interval 、date_sub、date_sub函数创建近7天的零时表：

    SELECT curdate() as createdAt
    union all
    SELECT date_sub(curdate(), interval 1 day) as createdAt
    union all
    SELECT date_sub(curdate(), interval 2 day) as createdAt
    union all
    SELECT date_sub(curdate(), interval 3 day) as createdAt
    union all
    SELECT date_sub(curdate(), interval 4 day) as createdAt
    union all
    SELECT date_sub(curdate(), interval 5 day) as createdAt
    union all
    SELECT date_sub(curdate(), interval 6 day) as createdAt
复制代码

先看看效果

然后在另一张表，查出你需要的数据：

SELECT DATE_FORMAT( created_at,'%Y-%m-%d') access_day,count(*) access_num FROM access_logs GROUP BY access_day
复制代码

看下结果：

最后，连接两张表查出你需要的数据：

select a.created_at as label,b.access_num as value
from(
    SELECT curdate() as created_at
    union all
    SELECT date_sub(curdate(), interval 1 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 2 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 3 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 4 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 5 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 6 day) as created_at
) a left join (
SELECT DATE_FORMAT( created_at,'%Y-%m-%d') access_day,count(*) access_num FROM access_logs GROUP BY access_day
) b on a.created_at = b.access_day order by a.created_at asc;
复制代码

这时会发现,没有记录的数据value返回的null，此时需要使用ifnull函数，将null设置为0

select a.created_at as label,IFNULL(b.access_num, 0) as value
from(
    SELECT curdate() as created_at
    union all
    SELECT date_sub(curdate(), interval 1 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 2 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 3 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 4 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 5 day) as created_at
    union all
    SELECT date_sub(curdate(), interval 6 day) as created_at
) a left join (
SELECT DATE_FORMAT( created_at,'%Y-%m-%d') access_day,count(*) access_num FROM access_logs GROUP BY access_day
) b on a.created_at = b.access_day order by a.created_at asc;
复制代码

此时 mysql查询就完成了。

下面是需要在gorm中使用，为了更好的使用体验，我们肯定是要能指定任意天数的，就需要循环拼接天数的sql语句，下面给出我的方法。