当前位置:新注册送38元体验金 > 开户送38体验金不限id数据库 > mysql查询最近7天的数据,没有数据自动补0

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

文章作者:开户送38体验金不限id数据库 上传时间:2019-08-22

问题描述

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

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

 

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

开户送38体验金不限id 1

解决思路

  1. 思路一: 可以在自己的程序中做额外的补零处理

  2. 开户送38体验金不限id,思路二: 构建一个最近七天的结果集,然后和查询的结果集合做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

开户送38体验金不限id 2

需要把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;

 

开户送38体验金不限id 3

本文由新注册送38元体验金发布于开户送38体验金不限id数据库,转载请注明出处:mysql查询最近7天的数据,没有数据自动补0

关键词: