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

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

原创最新推荐文章于 2023-06-09 03:42:00 发布 · 4.1k 阅读

4 ·

CC 4.0 BY-SA版权

SQL语句总结专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一种SQL技巧，用于在数据报告中补全最近七天内可能缺失的日期记录，确保数据完整性和分析准确性。通过创建临时日期表或使用子查询，可以有效地解决因某些日期无数据而导致的报表不连续问题。

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