mysql查询最近7天的数据，没有数据自动补0

佚名 7年前 (2018-08-30) Mysql 2596人围观抢沙发百度已收录

问题描述

查询数据库表中最近7天的记录

select count(*),date(create_time) as date from task where datediff(now(),create_time)<=6 group by day(create_time);

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

但是发现某一天没有数据，结果中没有显示当天(2017-08-28)的数据

解决思路

思路一：可以在自己的程序中做额外的补零处理
思路二：构建一个最近七天的结果集，然后和查询的结果集合做left join(本文采用第二种方式)

select a.click_date,b.count from ( SELECT curdate() as click_date union all SELECT date_sub(curdate(), interval 1 day) as click_date union all SELECT date_sub(curdate(), interval 2 day) as click_date union all SELECT date_sub(curdate(), interval 3 day) as click_date union all SELECT date_sub(curdate(), interval 4 day) as click_date union all SELECT date_sub(curdate(), interval 5 day) as click_date union all SELECT date_sub(curdate(), interval 6 day) as click_date ) a left join ( select date(create_time) as datetime, count(*) as count from arms_task group by date(create_time) ) b on a.click_date = b.datetime;

当天2017-08-28结果显示为NULL

需要把NULL设置为0，利用ifnull函数即可

select a.click_date,ifnull(b.count,0) as count from ( SELECT curdate() as click_date union all SELECT date_sub(curdate(), interval 1 day) as click_date union all SELECT date_sub(curdate(), interval 2 day) as click_date union all SELECT date_sub(curdate(), interval 3 day) as click_date union all SELECT date_sub(curdate(), interval 4 day) as click_date union all SELECT date_sub(curdate(), interval 5 day) as click_date union all SELECT date_sub(curdate(), interval 6 day) as click_date ) a left join ( select date(create_time) as datetime, count(*) as count from arms_task group by date(create_time) ) b on a.click_date = b.datetime;