c# - 将前一行值减去当前行并按日期排序

Question

我的表中数据如下

SELECT ID, VALUE, acc_no, adate 
FROM TB_DailyStatement

   id    value   acc_no       adate
    ---------------------------------------------
    1     12     1      2019-01-01 07:40:38.250
    2     14     1      2019-01-02 07:41:05.883
    3     15     1      2019-01-13 07:41:22.377
    4     10     2      2019-01-14 08:15:53.403
    5     16     2      2019-01-03 13:52:47.347 
    6     19     1      2019-01-09 13:53:56.317
    7      7     3      2019-01-17 00:00:00.000
    8     24     2      2019-01-17 00:00:00.000
    9     19     2      2019-01-02 00:00:00.000
    10     7     1      2019-01-07 00:00:00.000
    11    24     1      2019-01-05 14:12:47.080
    12    20     3      2019-01-28 00:00:00.000

预期结果

    id     value   acc_no       aDATE                   result 
    ------------------------------------------------------------------------
    1     12     1        2019-01-01 07:40:38.250        12 (current row values of acc_no=1) 
    2     14     1        2019-01-02 07:41:05.883         2 (14 (current row values)-12(previous row value of acc_no=1))  
   11     24     1        2019-01-05 14:12:47.080        10 (24 (current row values)-14(previous date  value of acc_no=1))  
   10      7     1        2019-01-07 00:00:00.000       -17 (7 (current row values)-24(previous date  value of acc_no=1))  
    6     19     1        2019-01-09 13:53:56.317        12 (19 (current row values)-7(previous date  value of acc_no=1))  
    3     15     1        2019-01-13 07:41:22.377        -4 (15 (current row values)-19(previous date  value of acc_no=1))  
    9     19     2        2019-01-02 00:00:00.000        19 (12 (current row values of acc_no=2) 
    5     16     2        2019-01-03 13:52:47.347        -3 (16 (current row values)-14(previous date  value of acc_no=2))  
    4     10     2        2019-01-14 08:15:53.403        -6 (10 (current row values)-16(previous date  value of acc_no=2))  
    8     24     2        2019-01-17 00:00:00.000        14 (24 (current row values)-10(previous date  value of acc_no=2))  
    7      7     3        2019-01-17 00:00:00.000         7 (12(current row values of acc_no=3) 
   12     20     3        2019-01-28 00:00:00.000        13 (20 (current row values)-7(previous date  value of acc_no=3)) 

我试过下面的查询

SELECT 
    id, t.value, acc_no, adate, 
    t.value - ISNULL(v.value, 0) AS result 
FROM 
    TB_DailyStatementt
OUTER APPLY 
    (SELECT TOP (1) value
     FROM TB_DailyStatement
     WHERE id < t.id
       AND acc_no = t.acc_no
     ORDER by id DESC) v

这会返回一些输出，但我无法使用 order by 子句，即 adate 和 acc_no

Answer 1

如果你想得到这个结果，只需按 acc_no asc 简单排序，你就可以获得简单的预期结果，

例如

根据acc_no acs 从表order 中选择*