如何用单条SQL语句计算近2年各物品的历史累计数量？

现有存储2000年起历史数据的表A，表结构仅包含日期列dt和物品列item，数据示例如下：

dt,   item
1/20/2000, A1
1/20/2000, A2
1/20/2000, A3
....
6/1/2024, A1

需求：计算近2年（共24个月）内，每个月截止时各物品自2000年1月1日起的累计数量，期望输出格式如下：

202406, A1, Count of A1 until 6/1/2024 since 1/1/2000
202406, A2, Count of A2 until 6/1/2024 since 1/1/2000
...
202206, A3, Count of A3 until 6/1/2022 since 1/1/2000

目前已实现的单独SQL：

1. 统计单月累计数量的SQL：

select  202406,  item, count(item)
from table A
where dt < '6/1/2024'  -- 注：修正原语句中字段名错误，datetime应为dt
group by left(convert(varchar,dt,112),6), item

2. 生成近24个月列表的SQL：

select dt2=format(dateadd(month,2-n,getdate()),'yyyyMM')
from
(
  select top (24) n=row_number() over (order by (select null))
  from sys.objects  -- 注：修正原语句表名错误，sys.object应为sys.objects
) t

需求：将上述两条语句合并为单条SQL实现目标结果，无需使用存储过程。

可以通过生成近24个月的截止日期列表，再与表A关联统计的方式实现，具体SQL如下：

-- 生成近24个月的月份标识及对应截止日期
WITH MonthList AS (
    SELECT 
        MonthKey = FORMAT(DATEADD(month, 1 - n, GETDATE()), 'yyyyMM'),
        MonthEndDate = DATEFROMPARTS(YEAR(DATEADD(month, 1 - n, GETDATE())), MONTH(DATEADD(month, 1 - n, GETDATE())) + 1, 1)
    FROM (
        SELECT TOP (24) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM sys.objects
    ) t
)
-- 关联表A统计每个月份截止时的累计数量
SELECT
    ml.MonthKey,
    a.item,
    COUNT(a.item) AS CumulativeCount
FROM MonthList ml
LEFT JOIN TableA a 
    ON a.dt < ml.MonthEndDate  -- 统计该月份截止前的所有历史数据
GROUP BY ml.MonthKey, a.item
ORDER BY ml.MonthKey DESC, a.item;

关键说明： #

• MonthList CTE：生成近24个月的MonthKey（如202406）和对应的月份截止日期（比如统计6月的累计数据，用7月1日作为过滤条件，确保包含6月全月数据）。
• 关联统计：使用LEFT JOIN保证每个月份的所有物品都能被统计到，即使某个月份该物品无新增数据，累计数也会延续之前的结果；通过GROUP BY按月份和物品分组计算累计数量。
• 语法修正：修正了原语句中sys.object表名、datetime字段名的错误，避免执行报错。

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