sql - 在 SQL Server 中使用 ROW_NUMBER() 获取先前记录的值

Question

希望这对于那些在 SQL Server 方面更有经验的人来说足够简单。

我有一个客户贷款事件数据表，只要他们的帐户发生操作，该数据就会更新。例如，如果他们的限额增加，将使用新限额创建新记录。我希望能够创建他们的事件列表，其中事件金额是他们的新限制减去他们之前的限制。

目前我有以下内容，但我正在努力弄清楚如何访问以前的记录。

SELECT   
    CUSTOMER
    ,LEDGER
    ,ACCOUNT
    ,H.AMOUNT - COALESCE(X.AMOUNT, 0)
FROM 
    dbo.ACTIVITY H WITH (NOLOCK)
LEFT OUTER JOIN 
    (SELECT  
        CUSTOMER
        ,LEDGER
        ,ACCOUNT
        ,ACTIVITY_DATE
        ,AMOUNT
        ,ROW_NUMBER() OVER (PARTITION BY CUSTOMER, LEDGER, ACCOUNT ORDER BY ACTIVITY_DATE ASC) AS ROW_NUMBER
     FROM 
         dbo.ACTIVITY WITH (NOLOCK)) X ON H.CUSTOMER = X.CUSTOMER
                                          AND H.LEDGER = X.LEDGER
                                          AND H.ACCOUNT = X.ACCOUNT

所以基本上我只想减去 x.amount 如果它是以前的记录但是当我不知道它发生在哪一天时我不确定该怎么做。

我以为 Row_Number() 会帮助我，但我还是有点难过。

希望大家尽快收到你们的来信:)

干杯

Answer 1

这是一个只通过 dbo.Activity ONCE

的查询

    SELECT H.CUSTOMER
          ,H.LEDGER
          ,H.ACCOUNT
          ,MAX(H.ACTIVITY_DATE) ACTIVITY_DATE
          ,SUM(CASE X.I WHEN 1 THEN AMOUNT ELSE -AMOUNT END) AMOUNT
      FROM (SELECT CUSTOMER
                  ,LEDGER
                  ,ACCOUNT
                  ,ACTIVITY_DATE
                  ,AMOUNT
                  ,ROW_NUMBER() OVER (PARTITION BY CUSTOMER, LEDGER, ACCOUNT ORDER BY ACTIVITY_DATE DESC) AS ROW_NUMBER
             FROM dbo.ACTIVITY WITH (NOLOCK)
           ) H
CROSS JOIN (select 1 union all select 2) X(I)
     WHERE ROW_NUMBER - X.I >= 0
  GROUP BY H.CUSTOMER
          ,H.LEDGER
          ,H.ACCOUNT
          ,ROW_NUMBER - X.I;

这是我用来测试的一些数据的 DDL/DML

CREATE TABLE dbo.ACTIVITY(CUSTOMER int, LEDGER int, ACCOUNT int, ACTIVITY_DATE datetime, AMOUNT int)
INSERT dbo.ACTIVITY select
    1,2,3,GETDATE(),123 union all select
    1,2,3,GETDATE()-1,16 union all select
    1,2,3,GETDATE()-2,12 union all select
    1,2,3,GETDATE()-3,1 union all select
    4,5,6,GETDATE(),1000 union all select
    4,5,6,GETDATE()-6,123 union all select
    7,7,7,GETDATE(),99;

---

备选方案

使用子查询获取上一行的更传统方法:

  SELECT CUSTOMER, LEDGER, ACCOUNT, ACTIVITY_DATE,
         AMOUNT - ISNULL((SELECT TOP(1) I.AMOUNT
                            FROM dbo.ACTIVITY I
                           WHERE I.CUSTOMER = O.CUSTOMER
                             AND I.LEDGER = O.LEDGER
                             AND I.ACCOUNT = O.ACCOUNT
                             AND I.ACTIVITY_DATE < O.ACTIVITY_DATE
                        ORDER BY I.ACTIVITY_DATE DESC), 0) AMOUNT
    FROM dbo.ACTIVITY O
ORDER BY CUSTOMER, LEDGER, ACCOUNT, ACTIVITY_DATE;

或者 ROW_NUMBER() 两次数据并在它们之间加入

   SELECT A.CUSTOMER, A.LEDGER, A.ACCOUNT, A.ACTIVITY_DATE, 
          A.AMOUNT - ISNULL(B.AMOUNT,0) AMOUNT
     FROM (SELECT *, RN=ROW_NUMBER() OVER (partition by CUSTOMER, LEDGER, ACCOUNT
                                          order by ACTIVITY_DATE ASC)
            FROM dbo.ACTIVITY) A
LEFT JOIN (SELECT *, RN=ROW_NUMBER() OVER (partition by CUSTOMER, LEDGER, ACCOUNT
                                          order by ACTIVITY_DATE ASC)
            FROM dbo.ACTIVITY) B ON A.CUSTOMER = B.CUSTOMER
                              AND A.LEDGER = B.LEDGER
                              AND A.ACCOUNT = B.ACCOUNT
                              AND B.RN = A.RN-1 -- prior record
ORDER BY A.CUSTOMER, A.LEDGER, A.ACCOUNT, A.ACTIVITY_DATE;