SQL Server中基于前序值累加常量生成新列的需求

没问题，我来帮你搞定这个需求！核心思路是先按日期给数据排好序，然后基于首行的Revenue值，逐行累加500得到新列。下面给出两种可行的实现方式，你可以根据自己的场景选择：

方法一：使用递归CTE（逐行计算） #

这种方式逻辑清晰直观，适合需要逐行依赖前一行结果的场景：

-- 先确保临时表数据格式正确
SELECT '1/1/2019' AS Ddate, '5000' AS Revenue INTO #tempA 
UNION SELECT '1/2/2019', '4000' 
UNION SELECT '1/3/2019', '3000' 
UNION SELECT '1/4/2019', '2000' 
UNION SELECT '1/5/2019', '6000' 
UNION SELECT '1/6/2019', '4000' 
UNION SELECT '1/7/2019', '2000' 
UNION SELECT '1/8/2019', '3000' 
UNION SELECT '1/9/2019', '6000' 
UNION SELECT '1/10/2019', '5000';

-- 递归CTE实现逻辑
WITH SortedData AS (
    SELECT 
        CAST(Ddate AS DATE) AS Ddate, -- 转换为日期类型确保排序正确
        CAST(Revenue AS INT) AS Revenue, -- 转成数值类型用于计算
        ROW_NUMBER() OVER (ORDER BY CAST(Ddate AS DATE)) AS RowNum
    FROM #tempA
),
RecursiveCTE AS (
    -- 锚点：首行的RequiredNewColumn直接取Revenue的值
    SELECT 
        Ddate, 
        Revenue, 
        Revenue AS RequiredNewColumn,
        RowNum
    FROM SortedData
    WHERE RowNum = 1
    UNION ALL
    -- 递归：后续每行的值 = 前一行的RequiredNewColumn + 500
    SELECT 
        sd.Ddate, 
        sd.Revenue, 
        rc.RequiredNewColumn + 500,
        sd.RowNum
    FROM SortedData sd
    INNER JOIN RecursiveCTE rc ON sd.RowNum = rc.RowNum + 1
)
SELECT Ddate, Revenue, RequiredNewColumn
FROM RecursiveCTE
ORDER BY Ddate;

方法二：使用窗口函数（更高效的计算方式） #

如果数据量较大，递归可能会有性能瓶颈，这种方式直接通过首行值和行号计算，效率更高：

WITH SortedData AS (
    SELECT 
        CAST(Ddate AS DATE) AS Ddate,
        CAST(Revenue AS INT) AS Revenue,
        ROW_NUMBER() OVER (ORDER BY CAST(Ddate AS DATE)) AS RowNum,
        -- 获取首行的Revenue基准值
        FIRST_VALUE(CAST(Revenue AS INT)) OVER (ORDER BY CAST(Ddate AS DATE)) AS FirstRevenue
    FROM #tempA
)
SELECT 
    Ddate, 
    Revenue,
    -- 通过首行值 + 500*(行号-1) 直接计算递增值
    FirstRevenue + 500*(RowNum - 1) AS RequiredNewColumn
FROM SortedData
ORDER BY Ddate;

关键注意事项 #

• 日期排序：一定要确保Ddate是日期类型或者能正确排序的格式，代码中加入CAST(Ddate AS DATE)是为了避免字符串排序出错（比如'10/1/2019'字符串排序会排在'1/2/2019'前面，转换为日期类型就能正确按时间顺序排列）。
• 数值转换：原表中Revenue是字符串类型，必须转换为数值类型（INT/DECIMAL）才能进行加减运算，否则会触发类型错误。

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