mysql查询几天之前，或某个时间段之间的每天记录数量，不存在补全0

直接看SQL（非常简单，通俗易懂）

biz_requirement_order： 业务表名

create_time：业务表时间字段，依据这个字段统计数量

dates：统计日期的别名，可随意改

num：数量返回值别名，可随意改

t表：查询所有符合条件的日期
a表：业务表中根据日期分组，查询每天的记录数量
最后使用左连接查询，将两个集合合并返回最终结果

• 查询几天之前

SELECT t.dates, IFNULL(a.num, '0') AS num FROM (
　　　　SELECT ( CURDATE() - INTERVAL n.num DAY) AS dates FROM
                     (SELECT 0 AS num
                     UNION ALL SELECT 1
                     UNION ALL SELECT 2
                     UNION ALL SELECT 3
                     UNION ALL SELECT 4
                     UNION ALL SELECT 5
                     UNION ALL SELECT 6
                     UNION ALL SELECT 7
                     UNION ALL SELECT 8
                     UNION ALL SELECT 9) AS n
                WHERE ( CURDATE() - INTERVAL n.num DAY) > ( CURDATE() - INTERVAL 7 DAY )  GROUP BY dates
　　　　) t
　　　　LEFT JOIN (
　　　　　　　　SELECT COUNT(*) AS num, DATE_FORMAT(create_time, '%Y-%m-%d') AS dates FROM `biz_requirement_order`
　　　　　　　　GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')
　　　　) a ON t.dates = a.dates
　　　　ORDER BY t.dates

结果：查询当天至7天前之间每天的记录数量

• 查询某个时间段之间

SELECT t.dates, IFNULL(a.num, '0') AS num FROM (
　　　　SELECT ( DATE_FORMAT('2024-03-31', '%Y-%m-%d') - INTERVAL n.num DAY) AS dates FROM
　　　　　　　　　　　　(SELECT 0 AS num
                     UNION ALL SELECT 1
                     UNION ALL SELECT 2
                     UNION ALL SELECT 3
                     UNION ALL SELECT 4
                     UNION ALL SELECT 5
                     UNION ALL SELECT 6
                     UNION ALL SELECT 7
                     UNION ALL SELECT 8
                     UNION ALL SELECT 9
                     UNION ALL SELECT 10
                     UNION ALL SELECT 11
                     UNION ALL SELECT 12
                     UNION ALL SELECT 13
                     UNION ALL SELECT 14
                     UNION ALL SELECT 15
                     UNION ALL SELECT 16
                     UNION ALL SELECT 17
                     UNION ALL SELECT 18
                     UNION ALL SELECT 19
                     UNION ALL SELECT 20
                     UNION ALL SELECT 21
                     UNION ALL SELECT 22
                     UNION ALL SELECT 23
                     UNION ALL SELECT 24
                     UNION ALL SELECT 25
                     UNION ALL SELECT 26
                     UNION ALL SELECT 27
                     UNION ALL SELECT 28
                     UNION ALL SELECT 29
                     UNION ALL SELECT 30
                     UNION ALL SELECT 31) AS n
                GROUP BY dates HAVING dates BETWEEN '2024-03-01' AND '2024-03-31'
        ) t
        LEFT JOIN (
　　　　　　　　SELECT COUNT(*) AS num, DATE_FORMAT(create_time, '%Y-%m-%d') AS dates FROM `biz_requirement_order`
　　　　　　　　GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')
　　　　　) a ON t.dates = a.dates
        ORDER BY t.dates

结果：查询2024-03-01至2024-03-31之间每天的记录数量