SQL 自连接比较当前记录和上一个日期的记录

Question

从附加的交易表中，每笔交易的cardOldValue应该等于最后一笔交易的CardNewValue。

我想识别一些可疑卡的 TransactionID 和 CardNumber，这些卡的 CardOldValue 大于上一次交易的 CardNewValue。

在示例中，应捕获 transactionID '003' 和 cardNumber '1234'，因为它大于之前的交易 cardNewValue '50'。

以下是我到目前为止所拥有的，但它并没有给我我所追求的数据。显然我不太确定自加入是如何工作的:

SELECT 
    f1.TransactionID, f1.cardNumber, f1.cardOldValue, 
    f2.cardNewValue, f1.transactionDate, f2.transactionDate
FROM 
    Transaction f1, Transaction F2
WHERE 
    f2.transactionDate = (SELECT MAX(transactionDate) 
                          FROM Transaction
                          WHERE transactionDate < f1.transactionDate
                            AND cardNumber = f2.cardNumber
                            AND f1.cardOldValue > f2.cardNewValue)

我单独测试了子查询，它给了我比当前日期稍早的日期。那么您能否告诉我如何正确地自行加入表格？

Answer 1

-- Finding previous record without a LAG() function:
SELECT
    this.TransactionID, this.cardNumber , this.transactionDate
        , this.cardOldValue, this.cardNewValue 
    , prev.TransactionID, prev.transactionDate
        , prev.cardNewValue
FROM Transaction this
JOIN Transaction prev
        ON this.cardNumber = prev.cardNumber            -- same card
        AND prev.transactionDate < this.transactionDate -- older date
        AND NOT EXISTS ( SELECT *                       -- no dates in between
                FROM Transaction mid
                WHERE mid.cardNumber = this.cardNumber
                AND mid.transactionDate < this.transactionDate
                AND mid.transactionDate > prev.transactionDate
        )
WHERE this.cardOldValue > prev.cardNewValue -- suspect
        ;