基于 sql 服务器中的上一行修改当前行

Question

我有一个这样的结果集：

YearMonth  Sales
201411     100
201412     100
201501     100
201502     100
201503     100
201504     100
201505     100
201506     100
201507     100
201508     100

需要添加另一行，销售额比上个月增加 4%。例如，我的结果应该是

YearMonth  Sales  New Sales
201411     100    100.00
201412     100    104.00
201501     100    108.16
201502     100    112.49
201503     100    116.99
201504     100    121.67
201505     100    126.53
201506     100    131.59
201507     100    136.86
201508     100    142.33

请帮助我获得最好的方法。

Answer 1

为您的要求提供了完美的答案。花了很长时间才弄清楚。只需将 #Temp 表名称更改为表名，并验证列名即可。

DECLARE @nCurrentSale FLOAT
DECLARE @nYeatDate INT
DECLARE @nSale FLOAT
CREATE TABLE #TempNEW(YearMonth VARCHAR(10), Sales FLOAT, NewSale FLOAT) 
SELECT TOP 1 @nCurrentSale =  Sales FROM #Temp 
   ORDER BY (CAST('01/' + SUBSTRING (CAST(YearMonth AS VARCHAR), 5 , 2) + '/' + SUBSTRING (CAST(YearMonth AS 
        VARCHAR), 0 , 5) AS DATETIME)) ASC
 DECLARE Cursor1 CURSOR FOR
        SELECT YearMonth, Sales FROM #Temp
        ORDER BY (CAST('01/' + SUBSTRING (CAST(YearMonth AS VARCHAR), 5 , 2) + '/' + SUBSTRING (CAST(YearMonth AS 
            VARCHAR), 0 , 5) AS DATETIME)) ASC
    OPEN Cursor1
    FETCH NEXT FROM Cursor1 INTO @nYeatDate, @nSale
    WHILE @@FETCH_STATUS = 0
    BEGIN  
        INSERT INTO #TempNEW(YearMonth, Sales, NewSale) VALUES(@nYeatDate, @nSale, CAST(@nCurrentSale AS DECIMAL(12,2)))
        SET @nCurrentSale = @nCurrentSale + ((@nCurrentSale/100) * 4)
        FETCH NEXT FROM Cursor1 INTO @nYeatDate, @nSale
    END
    CLOSE Cursor1
    DEALLOCATE Cursor1
SELECT * FROM #TempNEW

通知我你的状态。

Answer 2

是的，有可能。但首先您必须更改表格并添加额外的列 NewSales，然后尝试使用此链接https://dba.stackexchange.com/questions/34243/update-row-based-on-match-to-previous-row 我想你可以通过这个链接来完成SQL Server 还支持一些"SQL Server 中具有持久值的计算列"使用它，您可以指定所需的公式，然后根据您的公式自动创建新列值

Answer 3

这里有

两个想法...不太清楚我是否理解用例...此外，此解决方案仅适用于SQL 2012及更高版本

所以给定表格

CREATE TABLE [dbo].[LagExample](
    [YearMonth] [nvarchar](100) NOT NULL,
    [Sales] [money] NOT NULL
) 

第一个相当简单，只是假设你想根据它之前的天数来衡量百分比增加的幅度......

;WITH cte
as
(
    SELECT YearMonth,
       ROW_NUMBER()  OVER (ORDER BY YearMonth) - 1 AS SalesEntry,
        cast(LAG(Sales, 1,Sales) OVER (ORDER BY YearMonth) as float) as Sales
    FROM LagExample
)
SELECT YearMonth, 
        Sales,
       cast(Sales * POWER(cast(1.04 as float), SalesEntry) AS decimal(10,2)) as NewSales
FROM cte

第二个使用递归 CTE 来计算您在月份中移动时的值。这里有一个关于递归 CTE 的好链接http://www.codeproject.com/Articles/683011/How-to-use-recursive-CTE-calls-in-T-SQL

;with data
as
(
    SELECT Lead(le.YearMonth, 1, null) OVER (ORDER BY le.YearMonth) as NextYearMonth, 
            cast(le.Sales as Decimal(10,4)) as Sales,
            le.YearMonth
    FROM LagExample le
)
,cte
as
(
    SELECT *
    FROM data
    Where YearMonth = '201411'
    UNION ALL
    SELECT 
       data.NextYearMonth, 
       cast(cte.Sales * 1.04 as Decimal(10,4)) as Sales,
       data.YearMonth
    From cte join 
        data on data.YearMonth = cte.NextYearMonth
)
SELECT YearMonth, cast(Sales as Decimal(10,2))
FROM cte
order by YearMonth