表MyTable有ID,time,val 3个字段,
现需取前10条记录且在一条记录中添加2个字段 下一条val(preVal)和两者差(Amplitude)(按时间(time)倒叙)
Sql如下:
select top 10 ROW_NUMBER() over(order by newTable.ID desc) as mn,newTable.rn,newTable.id,newTable.time as mytime,newTable.val,oldTable.val as preVal,CONVERT(money,newTable.val)-CONVERT(money, oldTable.val) as Amplitude from (select top 10 ROW_NUMBER() over(order by ID) as rn, id,time,val from MyTable order by id desc) as newTable,(select top 11 ROW_NUMBER() over(order by ID) as rn, id,time,val from MyTable order by id desc) as oldTable where newTable.rn=oldTable.rn+1
结果: