如何在SQL Server中编写逐行减去上一行Reserviert值的Menge列查询？

没问题，我来帮你搞定这个递推计算的需求！首先得明确：SQL里的行本身是没有固有顺序的，所以你必须先确定一个排序键（比如ID、日期这类能确定行顺序的列），才能正确实现上一行值的引用和计算。

下面我给你两种实用的实现方法，分别适用于不同场景：

方法一：用窗口函数（高效首选） #

如果你的递推逻辑可以转化为累计求和的形式（也就是当前行的Menge = 初始Menge值 - 之前所有行Reserviert的总和），那用窗口函数是最高效的，尤其适合大数据量的表。

示例代码 #

假设你的表名为StockData，结构包含排序用的ID、初始的OriginalMenge和Reserviert列：

-- 先创建示例数据（你可以替换成自己的表）
CREATE TABLE StockData (
    ID INT PRIMARY KEY,
    OriginalMenge INT,
    Reserviert INT
);

INSERT INTO StockData VALUES
(1, 100, 10),
(2, NULL, 15),
(3, NULL, 20),
(4, NULL, 5);

-- 计算递推后的Menge列
SELECT 
    ID,
    OriginalMenge,
    Reserviert,
    -- 第一行用初始Menge，后续行用初始值减去之前所有Reserviert的总和
    COALESCE(OriginalMenge,
             FIRST_VALUE(OriginalMenge) OVER(ORDER BY ID)
             - SUM(Reserviert) OVER(ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)) AS CalculatedMenge
FROM StockData
ORDER BY ID;

结果说明 #

执行后会得到：

方法二：递归CTE（适合复杂递推） #

如果你的递推逻辑更复杂（比如依赖前一行的多个列值），递归CTE会更灵活。需要注意的是，如果你的排序键不是连续的，要先给行分配连续的行号。

示例代码（排序键非连续的情况） #

WITH NumberedRows AS (
    -- 先给每行分配连续的行号，确保顺序正确
    SELECT 
        *,
        ROW_NUMBER() OVER(ORDER BY ID) AS RowNum
    FROM StockData
),
RecursiveCTE AS (
    -- 锚点：第一行的初始值
    SELECT 
        ID,
        OriginalMenge AS CalculatedMenge,
        Reserviert,
        RowNum
    FROM NumberedRows
    WHERE RowNum = 1
    
    UNION ALL
    
    -- 递归：用前一行的CalculatedMenge减去前一行的Reserviert
    SELECT 
        n.ID,
        r.CalculatedMenge - r.Reserviert AS CalculatedMenge,
        n.Reserviert,
        n.RowNum
    FROM NumberedRows n
    JOIN RecursiveCTE r ON n.RowNum = r.RowNum + 1
)
SELECT 
    n.ID,
    n.OriginalMenge,
    n.Reserviert,
    r.CalculatedMenge
FROM NumberedRows n
JOIN RecursiveCTE r ON n.RowNum = r.RowNum
ORDER BY n.ID;

适用场景 #

当你的递推规则不是简单的累计求和，而是需要基于前一行的多个计算结果时，递归CTE是更好的选择。不过它的性能不如窗口函数，所以小数据量或者复杂逻辑时用它更合适。

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