如何实现每月显示全部产品的SQL查询（含无销量产品）

嘿，我完全懂你遇到的这个痛点！之前帮同事处理过几乎一模一样的需求——就是要保证每个月份的所有产品都必须出现在结果里，哪怕当月完全没销量，对吧？核心问题其实是你的原始查询只从有销量的记录里取数据，自然会漏掉那些“零销量”的组合。下面给你一步步拆解解决方案：

核心思路：先构建完整的「月份-产品」维度组合 #

要解决这个问题，你得先创建一个包含所有需要展示的月份和所有产品的“基础维度表”，然后把它和你的销量数据做左连接，这样就能把没销量的情况也拉进来了。

步骤1：生成所有需要的月份列表 #

如果你的数据库里没有现成的日历表，用递归CTE（Common Table Expression）生成月份范围是最方便的。比如从你销量表中最早的订单月份，一直到当前月份：

WITH date_range AS (
    -- 取销量表中最早的月份作为起始点
    SELECT DATE_TRUNC('month', MIN(sale_date)) AS sale_month
    FROM sales
    UNION ALL
    -- 递归生成后续每个月
    SELECT DATE_TRUNC('month', sale_month + INTERVAL '1 month')
    FROM date_range
    -- 终止条件：不超过当前月份
    WHERE sale_month + INTERVAL '1 month' <= DATE_TRUNC('month', CURRENT_DATE)
)

这里的DATE_TRUNC('month', ...)是把日期截断到月份开头，不同数据库语法可能略有不同：比如MySQL用DATE_FORMAT(sale_date, '%Y-%m-01')，SQL Server用DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1)。

步骤2：生成「月份×产品」的全组合 #

接下来把上面的月份列表和你的产品表做交叉连接（CROSS JOIN），这样就能得到每个月每个产品的组合，不管有没有销量：

, all_product_months AS (
    SELECT 
        dr.sale_month,
        p.product_id,
        p.product_name
    FROM date_range dr
    -- 交叉连接所有产品
    CROSS JOIN products p
)

这里假设你的产品表叫products，包含product_id和product_name字段，如果产品是固定的少量几个，也可以手动用VALUES子句列出，但用产品表的好处是新增产品会自动同步到结果里。

步骤3：左连接销量数据并统计 #

最后把这个全组合表和你的销量表左连接，用COALESCE把null的销量转换成0（如果需要的话），再按月份和产品分组统计：

SELECT 
    apm.sale_month,
    apm.product_name,
    -- 把null销量转为0，方便展示
    COALESCE(SUM(s.amount), 0) AS total_sales
FROM all_product_months apm
-- 左连接销量表，关联条件是月份和产品ID
LEFT JOIN sales s 
    ON apm.sale_month = DATE_TRUNC('month', s.sale_date) 
    AND apm.product_id = s.product_id
GROUP BY apm.sale_month, apm.product_name
-- 按月份和产品排序，实现逐月递进展示
ORDER BY apm.sale_month, apm.product_name;

关键细节提醒 #

• 如果你的数据库不支持递归CTE（比如某些老版本MySQL），可以用数字辅助表生成月份：比如先创建一个包含0-11的数字表，然后用起始月份加上数字对应的月份数来生成范围。
• 如果你不需要展示所有历史月份，而是固定的最近N个月，可以直接在date_range里指定起始月份，比如SELECT '2024-01-01'::DATE AS sale_month作为起始点。
• 要是销量表中有多个同月份同产品的记录，一定要用SUM或者COUNT来聚合，不然会出现重复行。

这样跑出来的结果就会每个月都显示所有产品，哪怕当月没销量的产品也会显示0销量，完全符合你要的逐月递进展示需求啦！

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