SQL Server：如何修改现有查询获取各品类折扣商品月度销售数据

核心调整逻辑 #

1. 如需输出所有品类所有月份（含无销量的月份/品类）的完整结果，需要先生成「全品类×全年12个月」的维度笛卡尔积，确保无数据的组合也能展示，空值补0即可
2. 按「品类+月份」分组统计当月的总商品数、当月折扣商品数
3. 用窗口函数SUM() OVER()按品类分组、按月份排序累加，得到截止当前月的累计折扣商品数

完整实现代码（含无数据月份补全） #

WITH AllMonths AS (
    -- 生成统计年份的12个月份基础数据，可根据需要修改统计年份
    SELECT 1 AS MonthNum, DATENAME(MONTH, '2021-01-01') AS MonthName
    UNION ALL SELECT 2, DATENAME(MONTH, '2021-02-01')
    UNION ALL SELECT 3, DATENAME(MONTH, '2021-03-01')
    UNION ALL SELECT 4, DATENAME(MONTH, '2021-04-01')
    UNION ALL SELECT 5, DATENAME(MONTH, '2021-05-01')
    UNION ALL SELECT 6, DATENAME(MONTH, '2021-06-01')
    UNION ALL SELECT 7, DATENAME(MONTH, '2021-07-01')
    UNION ALL SELECT 8, DATENAME(MONTH, '2021-08-01')
    UNION ALL SELECT 9, DATENAME(MONTH, '2021-09-01')
    UNION ALL SELECT 10, DATENAME(MONTH, '2021-10-01')
    UNION ALL SELECT 11, DATENAME(MONTH, '2021-11-01')
    UNION ALL SELECT 12, DATENAME(MONTH, '2021-12-01')
),
AllCategoryMonth AS (
    -- 生成全品类×全月份的维度组合
    SELECT c.CategoryID, c.CategoryName, am.MonthNum, am.MonthName
    FROM Category c
    CROSS JOIN AllMonths am
),
MonthlySales AS (
    -- 统计每个品类每个月的实际销售数据
    SELECT
        c.CategoryID,
        MONTH(i.PurchaseDate) AS MonthNum,
        COUNT(*) AS MonthTotalItems,
        COUNT(CASE WHEN i.PurchaseType = 'Discounted' THEN 1 END) AS MonthDiscountedItems
    FROM Items i
    LEFT JOIN SubCategory sc WITH(NOLOCK) ON sc.SubCategoryID = i.SubCategoryID
    LEFT JOIN CategoryMapping cm WITH(NOLOCK) ON cm.SubCategoryID = i.SubCategoryID
    LEFT JOIN Category c WITH(NOLOCK) ON c.CategoryID = cm.CategoryID
    WHERE i.PurchaseDate BETWEEN '2021-01-01' AND '2021-12-31' -- 可替换为实际统计的日期范围
    GROUP BY c.CategoryID, MONTH(i.PurchaseDate)
)
-- 最终关联计算输出结果
SELECT
    acm.CategoryName,
    LEFT(acm.MonthName, 3) AS Month, -- 输出Jan/Feb格式的月份缩写，不需要缩写可直接取MonthName
    ISNULL(ms.MonthTotalItems, 0) AS [Count of Total Items],
    -- 窗口函数计算年初至当前月的累计折扣商品数
    SUM(ISNULL(ms.MonthDiscountedItems, 0)) OVER(PARTITION BY acm.CategoryName ORDER BY acm.MonthNum) AS [Count of Discounted Items],
    ISNULL(ms.MonthDiscountedItems, 0) AS [Count of Discounted Items Per Month]
FROM AllCategoryMonth acm
LEFT JOIN MonthlySales ms 
    ON acm.CategoryID = ms.CategoryID 
    AND acm.MonthNum = ms.MonthNum
ORDER BY acm.MonthNum, acm.CategoryName

简化版本（仅输出有销量的月份） #

如果不需要展示无销量的月份，可直接在原有查询基础上调整，代码更轻量：

select
CategoryName,
LEFT(DATENAME(MONTH, PurchaseDate),3) as [Month],
Count(*) as [Count of Total Items],
sum(count(case when PurchaseType = 'Discounted' then 1 else null end)) over(partition by CategoryName order by MONTH(PurchaseDate)) as [Count of Discounted Items],
count(case when PurchaseType = 'Discounted' then 1 else null end)  as [Count of Discounted Items Per Month]
from
 (select
i.ItemID,
i.PurchaseDate,
i.PurchaseType AS PurchaseType,
c.CategoryName AS CategoryName
from 
Items i
LEFT Join SubCategory sc with(nolock) on sc.SubCategoryID = i.SubCategoryID
LEFT Join CategoryMapping cm with(nolock) on cm.SubCategoryID  = i.SubCategoryID
LEFT Join Category c with(nolock) on c.CategoryID = cm.CategoryID     

where i.PurchaseDate > '01/01/2021' and i.PurchaseDate < '12/31/2021'
)as Data
group by CategoryName, MONTH(PurchaseDate), DATENAME(MONTH, PurchaseDate)
order by MONTH(PurchaseDate)

内容的提问来源于stack exchange，提问作者Mr.Human