Sql 运行总计到最新期间

好的，您已经一整天都没有答案了，所以即使我在TSQL中工作，我也会try 一种与ANSI SQL兼容的解决方案.如果我的语法有点错误，请与我合作.

在我们开始之前，请判断您的"期望输出"，您当前显示的是a在3月22日(共5日)的运行总数，但您在2月22日的a有一个2，所以应该是7的运行总数，对吗？

无论如何，假设这只是一个输入错误，我会制作一些CTE来构建一个列表，列出所有要报告的{PERIOD，NAME}对，然后将实际数据加入其中.有很多方法可以生成日期，如果您的实际数据相当可靠，最简单的方法是使用DISTINCT，但如果该假设不适用于您的数据，我可以描述其他方法.

因此，考虑到所有这些，这里是我的解决方案.为了便于移植，我将示例数据放在CTE中，只需将我的"cteTabA"替换为数据表的真正名称

--Code sample data as a CTE for portability
;with cteTabA as ( 
    SELECT * 
    FROM ( VALUES 
        ('A', '02/2022', '2')
        , ('A', '03/2022', '5')
        , ('A', '04/2022', '3')
        , ('A', '05/2022', '7')
        , ('B', '02/2022', '9')
        , ('B', '04/2022', '6')
    ) as TabA(Name, Period, Value) 
) --END of sample data, actual query below
--First, build a list of periods to use. If your data set is full, just select DISTINCT
, cteDates as ( --but there are other ways if this doesn't work for you - let me know!
    SELECT DISTINCT Period FROM cteTabA
) --Next, build a list of names to report on
, cteNames as (
    SELECT DISTINCT Name FROM cteTabA
) --Now build your table that has all periods for all names
, cteRepOn as (
    SELECT * FROM cteNames CROSS JOIN cteDates 
)--Now assemble a table that has entries for each period for each name,
--but fill in zeroes for those you don't actually have data for
, cteFullList as (
    SELECT L.*, COALESCE(D.Value, 0) as Value
    FROM cteRepOn as L 
        LEFT OUTER JOIN cteTabA as D on L.Name = D.Name AND L.Period = D.Period 
)--Now your query works as expected with the gaps filled in
SELECT PERIOD, NAME, Value 
    ,SUM(Value) OVER (PARTITION BY NAME ORDER BY PERIOD) AS balance
FROM  cteFullList
WHERE Period < '06/2022'--CURRENT_DATE()
ORDER BY NAME, PERIOD

这将产生如下输出