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

 

 

select DATE( createtime) date , createtime, count(1) as count   from  order表  where DATEDIFF( now(), createtime)<=7
 group by date ;

12 月九号没有; 

给他在关联一张日期的表; 

--生成从今天开始完整的7天日期

DECLARE @LastSevenDay table ( day date ) DECLARE @StartDay date = GETDATE(); DECLARE @InsertDay date = @StartDay; WHILE DATEDIFF(DAY,@InsertDay,@StartDay) < 7 BEGIN INSERT INTO @LastSevenDay VALUES ( @InsertDay) SET @InsertDay = DATEADD(day,-1,@InsertDay) END --生成最后结果 SELECT DATEPART(day,lsd.day) AS '日期' ,COUNT(orders.id) AS '订单量' FROM @LastSevenDay AS lsd LEFT JOIN orders ON lsd.day = CONVERT(varchar(10),orders.ordertime,120) GROUP BY lsd.day

 但是呢, 我们不想这么麻烦;  一个SQL 就可以;  

 

套一层 : 

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 (
  -- 原来的sql
select DATE( createtime) date , createtime, count(1) as count   from order表  where DATEDIFF( now(), createtime)<=7
 group by date 

) b on a.click_date = b.date;

参看自: https://blog.csdn.net/ljxbbss/article/details/78028424