如何在MySQL中返回近7天每日交易统计（无交易日期返回0）

核心思路 #

原有查询仅返回有交易记录的日期，原因是无交易的日期在原表中不存在对应数据。要解决这个问题，需要先构造出目标时间范围内的完整日期序列，再通过左连接关联交易表，最后将空统计值填充为0。

方案一：MySQL 8.0+（支持递归CTE） #

用递归CTE生成连续日期，再左连接交易表统计：

WITH RECURSIVE date_range AS (
    -- 生成过去7天的起始日期（比如今天往前推6天）
    SELECT CURDATE() - INTERVAL 6 DAY AS date_val
    UNION ALL
    -- 递归生成后续日期，直到今天
    SELECT date_val + INTERVAL 1 DAY 
    FROM date_range 
    WHERE date_val < CURDATE()
)
SELECT
    dr.date_val AS time,
    IFNULL(COUNT(t.transaction_id), 0) AS orders,
    IFNULL(SUM(t.amount), 0) AS sales
FROM date_range dr
LEFT JOIN tbltransactions t 
    ON CAST(t.time AS DATE) = dr.date_val
GROUP BY dr.date_val
ORDER BY dr.date_val ASC;

如果需要用自定义参数替代固定日期范围，修改CTE的起始和结束条件即可：

WITH RECURSIVE date_range AS (
    SELECT CAST(? AS DATE) AS date_val
    UNION ALL
    SELECT date_val + INTERVAL 1 DAY 
    FROM date_range 
    WHERE date_val < CAST(? AS DATE)
)
-- 后续查询逻辑同上

方案二：兼容MySQL 5.x（无CTE支持） #

用数字表生成7个日期，再左连接统计：

SELECT
    date_val AS time,
    IFNULL(COUNT(t.transaction_id), 0) AS orders,
    IFNULL(SUM(t.amount), 0) AS sales
FROM (
    -- 生成过去7天的日期序列
    SELECT CURDATE() - INTERVAL (n-1) DAY AS date_val
    FROM (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    ) nums
) date_range
LEFT JOIN tbltransactions t 
    ON CAST(t.time AS DATE) = date_range.date_val
GROUP BY date_val
ORDER BY date_val ASC;

关键细节 #

• 左连接：确保日期序列中的每一行都被保留，即使交易表无对应数据
• IFNULL/COALESCE：将无交易时的NULL统计结果替换为0
• 时间范围调整：根据需求修改CURDATE() - INTERVAL 6 DAY或参数值，适配自定义时间区间

内容的提问来源于stack exchange，提问作者warre