如何写SQL计算不同日期的金额差

Question

有一张表t，字段1交易金额，字段2交易日期。怎么写SQL查询交易日期为今天和去年的交易金额之差

Answer 1

row_number 排序，然后用 cte 递归就好

with t1 as (
    select *,row_number() over(order by 交易日期) dtid from t
),t2 as (
    select *,0 as 差额 from t1 where dtid = 1
    union all
    select t1.*,t1.交易金额-t2.交易金额 from t1,t2 where t2.dtid=t1.dtid + 1
)
select * from t2