创建从-1开始的行索引列

Question

我需要创建一个从-1开始的行索引列，以便查询前一天的余额。我现在的问题是：

select TRANSDATE, sum(convert(float,AMOUNTMST-SETTLEAMOUNTMST)) as Balance

from [AX2cTestStage].[dbo].[CUSTTRANS_V]

group by TRANSDATE

order by TRANSDATE asc

TRANSDATE                Balance
2019-04-12 00:00:00.000 -22591.47
2019-04-15 00:00:00.000 -394.95
2019-04-25 00:00:00.000 -1776
2019-04-26 00:00:00.000 -11973.84
2019-04-29 00:00:00.000 -24230.16
2019-05-02 00:00:00.000 -10695.39

这就是我需要的：

TRANSDATE                Balance   Row Index
2019-04-12 00:00:00.000 -22591.47    -1
2019-04-15 00:00:00.000 -394.95       0
2019-04-25 00:00:00.000 -1776         1 
2019-04-26 00:00:00.000 -11973.84     2
2019-04-29 00:00:00.000 -24230.16     3
2019-05-02 00:00:00.000 -10695.39     4

我曾尝试将一个变量声明为行索引

declare @row_num as int = -1
select TRANSDATE, sum(convert(float,AMOUNTMST-SETTLEAMOUNTMST)) as Balance, @row_num += 1 as Row Index

from [AX2cTestStage].[dbo].[CUSTTRANS_V]

group by TRANSDATE

我收到这个错误：

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

在为每个字段声明了一个变量之后，我仍然收到错误。有没有更简单的方法来实现这一点？谢谢

Answer 1

您可以使用ROW_NUMBER()。例如：

select
  TRANSDATE, 
  sum(convert(float,AMOUNTMST-SETTLEAMOUNTMST)) as Balance, 
  row_number() over(order by TRANSDATE) - 2 as Row Index
from [AX2cTestStage].[dbo].[CUSTTRANS_V]
group by TRANSDATE