mysql 7天自动拒单功能,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 @LastSevenDaytable

(

day date

)

DECLARE @StartDaydate = GETDATE();

DECLARE @InsertDaydate = @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 @LastSevenDayAS 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