mysql - 只选择部分数据时如何计算运行总SQL语句？

Question

如何在SQL语句中选择ID 6-9并计算累计？

这将在图片中显示相同的运行总计，并且不会从 ID 6 开始:

Answer 1

如果 SQL Server 2012+ 你可以使用窗口函数

Declare @YourTable table (ID int,Amount int)
Insert Into @YourTable values (1,80),(2,11),(3,123),(4,47),(5,8),(6,500),(7,49),(8,27),(9,34),(10,56),(11,82),(12,17)

-- If 2012+  
Select *
      ,RunningTotal = sum(Amount) over (Order By ID)
 From @YourTable
 Where ID between 6 and 9

返回

ID  Amount  RunningTotal
6   500     500
7   49      549
8   27      576
9   34      610

如果低于 2012

Select A.ID
      ,A.Amount
      ,RunningTotal = sum(B.Amount)
 From @YourTable A
 Join @YourTable B on (B.ID <=A.ID and B.ID between 6 and 9)
 Where A.ID between 6 and 9
 Group By A.ID,A.Amount
 Order By A.ID