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