如何高效统计未来12个月各订单中商品的月度数量？

你的原始方案通过多次左连接子查询实现单月统计，但扩展到12个月时会出现代码冗余、重复扫描Orders表的问题，查询效率和可维护性都很差。下面提供两种更高效的优化方案，同时修正原查询中日期判断的潜在漏洞（比如跨年月份的统计错误）。

方案一：条件聚合（推荐，灵活易维护） #

利用CASE语句结合聚合函数，仅需扫描一次Orders表就能完成所有月份的统计，无需多次关联。

SELECT 
    I.ItemNumber,
    -- 按「月份 年份后两位」格式逐个统计未来12个月的数量
    SUM(CASE WHEN O.[Shipment Date] >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) 
             AND O.[Shipment Date] < DATEADD(mm, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) 
             THEN O.Quantity ELSE 0 END) AS [August 22],
    SUM(CASE WHEN O.[Shipment Date] >= DATEADD(mm, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) 
             AND O.[Shipment Date] < DATEADD(mm, 2, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) 
             THEN O.Quantity ELSE 0 END) AS [September 22],
    SUM(CASE WHEN O.[Shipment Date] >= DATEADD(mm, 2, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) 
             AND O.[Shipment Date] < DATEADD(mm, 3, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) 
             THEN O.Quantity ELSE 0 END) AS [October 22],
    -- 按上述格式继续添加剩余9个月的统计语句
    SUM(CASE WHEN O.[Shipment Date] >= DATEADD(mm, 11, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) 
             AND O.[Shipment Date] < DATEADD(mm, 12, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) 
             THEN O.Quantity ELSE 0 END) AS [July 23]
FROM 
    Items I
LEFT JOIN 
    Orders O ON I.ItemNumber = O.Itemno
GROUP BY 
    I.ItemNumber

关键说明： #

• 用DATEFROMPARTS和DATEADD精准锁定每个月份的时间区间，彻底避免跨年时的年份判断错误；
• 无订单的商品对应月份会显示0，完全匹配你的输出需求；
• 仅扫描一次Orders表，性能远优于多次左连接的原始方案。

方案二：使用PIVOT运算符（SQL Server专属） #

如果你的数据库是SQL Server，可利用PIVOT语法将行数据转为列，代码结构更简洁。

-- 先聚合每个商品每月的总数量
WITH MonthlyItemQty AS (
    SELECT 
        I.ItemNumber,
        -- 生成「月份 年份后两位」的列名格式
        FORMAT(O.[Shipment Date], 'MMMM yy') AS MonthYear,
        SUM(O.Quantity) AS TotalQty
    FROM 
        Items I
    LEFT JOIN 
        Orders O ON I.ItemNumber = O.Itemno
    WHERE 
        O.[Shipment Date] >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
        AND O.[Shipment Date] < DATEADD(mm, 12, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
    GROUP BY 
        I.ItemNumber, FORMAT(O.[Shipment Date], 'MMMM yy')
)
-- 将行转列为目标格式
SELECT 
    ItemNumber,
    [August 22], [September 22], [October 22], -- 列出未来12个月的MonthYear值
    -- 补充剩余月份的列名
    [July 23]
FROM 
    MonthlyItemQty
PIVOT (
    SUM(TotalQty)
    FOR MonthYear IN ([August 22], [September 22], [October 22], [July 23])
) AS PivotTable

关键说明： #

• 先通过CTEMonthlyItemQty聚合得到每个商品每月的数量，生成包含MonthYear的中间结果；
• PIVOT将MonthYear的行值直接转为列，快速得到目标输出格式；
• 若需要适配任意起始月份，可结合动态SQL自动生成列名，进一步提升灵活性。

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