mysql查询最近7天的数据，没有数据自动补0 ，用于echarts图展示

最新推荐文章于 2023-06-09 03:42:00 发布

普通小程序员

最新推荐文章于 2023-06-09 03:42:00 发布

阅读量1.3k

点赞数 1

分类专栏： Mybatis

原文链接：https://blog.csdn.net/sq_com/article/details/84965326

版权

Mybatis 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

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;