SQL Developer查询：无对应月份时返回0的销售单价实现方案

核心思路是先构造包含1-12月的完整月份列表，再通过左连接关联你的订单数据表，最后用函数将无数据的月份值转换为0。以下是主流数据库的具体实现：

MySQL 实现 #

-- 生成1-12月的完整月份列表
WITH months AS (
    SELECT 1 AS month 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
)
SELECT 
    m.month,
    COALESCE(AVG(o.sales_price), 0) AS avg_sales_price -- 按需求替换为SUM或其他聚合函数
FROM months m
LEFT JOIN your_order_table o 
    ON MONTH(o.order_date) = m.month -- 关联订单表的月份字段
GROUP BY m.month
ORDER BY m.month;

SQL Server 实现 #

WITH months AS (
    SELECT 1 AS month
    UNION ALL
    SELECT month + 1 FROM months WHERE month < 12
)
SELECT 
    m.month,
    ISNULL(AVG(o.sales_price), 0) AS avg_sales_price
FROM months m
LEFT JOIN your_order_table o 
    ON MONTH(o.order_date) = m.month
GROUP BY m.month
ORDER BY m.month;

PostgreSQL 实现 #

WITH months AS (
    SELECT generate_series(1,12) AS month
)
SELECT 
    m.month,
    COALESCE(AVG(o.sales_price), 0) AS avg_sales_price
FROM months m
LEFT JOIN your_order_table o 
    ON EXTRACT(MONTH FROM o.order_date) = m.month
GROUP BY m.month
ORDER BY m.month;

注意事项 #

• 替换your_order_table为你的实际订单表名
• 替换order_date为订单表中存储日期的字段名
• sales_price替换为实际的销售单价字段
• 如果需要按年份筛选，可在LEFT JOIN的关联条件中添加YEAR(o.order_date) = 2024（替换为目标年份），避免跨年份数据干扰

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