SQL Server中如何实现基于上月值计算Aviable列？

我来帮你搞定这个Aviable列的计算需求！首先我们得先处理原始数据——因为同一个月份有多条支出记录，第一步必须先按月做聚合，计算每个月的总支出、总税费，再基于这个聚合结果来计算可用预算。

方法一：使用窗口函数SUM（推荐，简洁高效） #

这个方法的核心思路是：Aviable其实就是初始预算减去从首月到当前月的累计支出+税费总和。首月的累计就是当月的支出税费总和，后续月份的累计是之前所有月份的总和，所以直接用窗口函数SUM() OVER(ORDER BY Month)就能快速计算出累计值，再用初始预算减去这个累计值就是每个月的Aviable。

完整SQL代码如下：

WITH MonthlyAgg AS (
    SELECT 
        Month,
        MAX(Budget) AS MonthlyBudget,  -- 同一个月的Budget值一致，用MAX/AVG都可以
        SUM(Spend) AS TotalSpend,
        SUM(Taxes) AS TotalTaxes,
        SUM(Spend + Taxes) AS TotalOutflow  -- 当月总支出（支出+税费）
    FROM Budget_Spend
    GROUP BY Month
)
SELECT 
    Month,
    MonthlyBudget,
    TotalSpend,
    TotalTaxes,
    -- 初始预算减去累计总支出，得到当月可用预算
    MonthlyBudget - SUM(TotalOutflow) OVER(ORDER BY Month) AS Aviable
FROM MonthlyAgg
ORDER BY Month;

结果验证： #

完全符合你的需求：首月1000 - (100+15) = 885，次月885 - (200+30) = 655，以此类推。

方法二：使用LAG函数结合递归CTE（适合理解逐月推导逻辑） #

如果你一定要用LAG函数，需要注意：SQL Server不允许在同一个SELECT语句中直接用LAG引用刚计算的列，所以我们可以用递归CTE来实现逐月推导：

WITH MonthlyAgg AS (
    SELECT 
        Month,
        MAX(Budget) AS MonthlyBudget,
        SUM(Spend) AS TotalSpend,
        SUM(Taxes) AS TotalTaxes,
        SUM(Spend + Taxes) AS TotalOutflow
    FROM Budget_Spend
    GROUP BY Month
),
RecursiveAviable AS (
    -- 首月的可用预算
    SELECT 
        Month,
        MonthlyBudget,
        TotalSpend,
        TotalTaxes,
        MonthlyBudget - TotalOutflow AS Aviable
    FROM MonthlyAgg
    WHERE Month = 1
    UNION ALL
    -- 后续月份用上月可用预算减去当月总支出
    SELECT 
        ma.Month,
        ma.MonthlyBudget,
        ma.TotalSpend,
        ma.TotalTaxes,
        ra.Aviable - ma.TotalOutflow AS Aviable
    FROM MonthlyAgg ma
    JOIN RecursiveAviable ra ON ma.Month = ra.Month + 1
)
SELECT Month, MonthlyBudget, TotalSpend, TotalTaxes, Aviable
FROM RecursiveAviable
ORDER BY Month;

这个方法的逻辑更贴近你描述的“首月计算初始值，后续月份基于上月值推导”，但性能上不如第一种窗口函数的方法，尤其是数据量大的时候。

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