如何获取包含0值的近6个月商品销售统计数据？

要让无销售记录的月份也显示0值，核心是先生成近6个月的完整月份列表，再将其与你的销售统计结果做左连接，确保每个月份都能出现在最终结果里。

步骤1：生成近6个月的月份列表 #

用递归CTE（MySQL 8.0+支持）生成连续的6个月份，格式和你原查询中的'%Y %M'一致：

WITH RECURSIVE months AS (
    SELECT DATE_FORMAT(NOW() - INTERVAL 5 MONTH, '%Y %M') AS month_val
    UNION ALL
    SELECT DATE_FORMAT(STR_TO_DATE(month_val, '%Y %M') + INTERVAL 1 MONTH, '%Y %M')
    FROM months
    WHERE STR_TO_DATE(month_val, '%Y %M') < NOW()
)
SELECT * FROM months;

这段代码会输出从5个月前到当前月的所有月份，比如当前是2024年10月，就会输出2024 May到2024 October共6行。

步骤2：关联销售统计数据 #

将上面的月份列表作为主表，左连接你的销售统计查询，用COALESCE把空值转为0：

WITH RECURSIVE months AS (
    SELECT DATE_FORMAT(NOW() - INTERVAL 5 MONTH, '%Y %M') AS month_val
    UNION ALL
    SELECT DATE_FORMAT(STR_TO_DATE(month_val, '%Y %M') + INTERVAL 1 MONTH, '%Y %M')
    FROM months
    WHERE STR_TO_DATE(month_val, '%Y %M') < NOW()
)
SELECT
    '00000004357' AS product_id,
    COALESCE(sales.unit_qty, 0) AS unit_qty,
    months.month_val AS MONTH
FROM months
LEFT JOIN (
    -- 你的原统计查询
    SELECT
        pharmacy_bulk_issue_details.product_id,
        SUM(pharmacy_bulk_issue_details.qty - pharmacy_bulk_issue_details.returened_qty) AS unit_qty,
        DATE_FORMAT(pharmacy_bulk_issue.added_time, '%Y %M') AS MONTH
    FROM pharmacy_bulk_issue_details
    LEFT JOIN pharmacy_bulk_issue ON pharmacy_bulk_issue_details.bulk_id = pharmacy_bulk_issue.id
    WHERE
        pharmacy_bulk_issue_details.product_id = '00000004357' 
        AND pharmacy_bulk_issue.added_time >= NOW() - INTERVAL 6 MONTH
    GROUP BY
        pharmacy_bulk_issue_details.product_id, 
        DATE_FORMAT(pharmacy_bulk_issue.added_time, '%Y %M')
) AS sales ON months.month_val = sales.MONTH;

兼容低版本MySQL #

如果你的MySQL版本低于8.0，不支持CTE，可以用UNION ALL手动生成月份：

SELECT DATE_FORMAT(NOW() - INTERVAL 5 MONTH, '%Y %M') AS month_val UNION ALL
SELECT DATE_FORMAT(NOW() - INTERVAL 4 MONTH, '%Y %M') UNION ALL
SELECT DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y %M') UNION ALL
SELECT DATE_FORMAT(NOW() - INTERVAL 2 MONTH, '%Y %M') UNION ALL
SELECT DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y %M') UNION ALL
SELECT DATE_FORMAT(NOW(), '%Y %M')

把这段替换掉CTE部分即可。

关键注意点 #

• 必须保证月份格式完全一致（比如%Y %M是英文月份名，不要和数字格式混淆），否则关联会失败。
• 主表用月份列表而非销售表，才能确保所有月份都被保留。

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