mysql查詢最近7天的數據，沒有數據自動補0

問題描述

查詢資料庫表中最近7天的記錄

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

但是發現某一天沒有數據，結果中沒有顯示當天(2017-08-28)的數據

解決思路

1. 思路一： 可以在自己的程式中做額外的補零處理

2. 思路二： 構建一個最近七天的結果集，然後和查詢的結果集合做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;