SQL查询优化：如何在同语句中统计指定年月及全时段产品销量？

方法一：子查询关联（直观易维护） #

先通过子查询统计每个产品的全时段销售总数，再与原查询结果关联，既保留指定年月的统计逻辑，又能获取全时段数据：

SELECT 
    pCode, 
    [Products].prodTitle,
    COUNT(*) AS Sales,
    SUM(amountBilled) AS SalesValue,
    TotalSales.TotalAllTimeSales
FROM Orders
INNER JOIN Content ON pCode = [Products].prodCode
-- 子查询计算全时段销售总量
INNER JOIN (
    SELECT 
        pCode, 
        COUNT(*) AS TotalAllTimeSales
    FROM Orders
    INNER JOIN Content ON pCode = [Products].prodCode
    GROUP BY pCode
) AS TotalSales ON TotalSales.pCode = pCode
WHERE DatePart(Month, orderDate) = '3'
AND DatePart(Year, orderDate) = '2024'
GROUP BY pCode, [Products].prodTitle, TotalSales.TotalAllTimeSales

方法二：窗口函数（代码更简洁） #

利用窗口函数COUNT() OVER()直接计算每个产品的全时段销售数，同时通过条件聚合统计指定年月的销售数据，避免额外关联操作：

SELECT 
    DISTINCT
    pCode, 
    [Products].prodTitle,
    -- 统计指定年月的销售数量
    SUM(CASE WHEN DATEPART(Month, orderDate) = '3' AND DATEPART(Year, orderDate) = '2024' THEN 1 ELSE 0 END) OVER(PARTITION BY pCode) AS Sales,
    -- 统计指定年月的销售金额
    SUM(CASE WHEN DATEPART(Month, orderDate) = '3' AND DATEPART(Year, orderDate) = '2024' THEN amountBilled ELSE 0 END) OVER(PARTITION BY pCode) AS SalesValue,
    -- 统计全时段总销售数量
    COUNT(*) OVER(PARTITION BY pCode) AS TotalAllTimeSales
FROM Orders
INNER JOIN Content ON pCode = [Products].prodCode

注意事项 #

• 若pCode不是产品唯一标识，需调整PARTITION BY或GROUP BY的字段（比如加上prodTitle），防止统计错误。
• 存储过程中需将固定的'3'和'2024'替换为对应参数（如@Month、@Year）。

内容的提问来源于stack exchange，提问作者Jon.A