用上年度利润相加计算连续年度利润

Question

示例数据和预期结果如下图所示：

​

​

我们需要将上一年的利润与连续的一年相加，并以图像中给出的格式显示数据(图像中还提供了样本数据)。

请帮助我使用SQL查询来解决这个问题。

Answer 1

您也可以使用窗口函数来编写此代码。

SELECT
    Year,
    SUM(Profit) OVER(ORDER BY Year) AS Total_Profit
FROM your_table
ORDER BY Year

Answer 2

这可能是世界上最简单的递归CTE，你可以用谷歌搜索一下。但事实是：

declare @years table(y int, p int)
insert @years values (2015,1000),(2016,2000),(2017,500),(2018,1000)
; with cumulative as
(
    select top 1 * from @years order by y
    union all
    select y.y, y.p+c.p
    from @years y
    join cumulative c on y.y=c.y+1
)
select * from cumulative

结果：

y       p
2015    1000
2016    3000
2017    3500
2018    4500

Answer 3

使用分区求和：

WITH V1 AS (
    SELECT 2015 AS YEAR, 1000 AS PROFIT FROM DUAL
    UNION ALL SELECT 2016 AS YEAR, 2000 AS PROFIT FROM DUAL
    UNION ALL SELECT 2017 AS YEAR, 500 AS PROFIT FROM DUAL
    UNION ALL SELECT 2018 AS YEAR, 1000 AS PROFIT FROM DUAL
)
SELECT 
    V1.YEAR
    , PROFIT --- You can comment it if not needed
    , SUM(PROFIT) OVER (PARTITION BY 1 ORDER BY YEAR RANGE UNBOUNDED PRECEDING) AS PROFIT_CUM
FROM V1;