SQL 返回带有上次付款日期的交易表

Question

我在 Access 中有一个查询，该查询返回所有客户的交易 字段为

TransID、TenantID、TransactionType、Amount、TransactionDate

其中 TransactionType =1 表示费用，2 表示付款

我想返回此查询的所有记录，并附加一个包含之前付款的 (TransactionType =2 ) TransactionDate 的字段客户端（租户ID） 如果没有之前的付款，我只想返回之前交易日期为空的记录。

你能帮我吗？

Answer 1

使用子查询，传入外部查询的交易日期：

SELECT TransID, TenantID, TransactionType, Amount, TransactionDate,
       (SELECT MAX(I.TransactionDate)
        FROM unnamed_table I
        WHERE I.TransactionDate < O.TransactionDate
          AND I.TransactionType = 2
          AND I.TenantID = O.TenantID) PrevTransDate
FROM unnamed_table O

Answer 2

select t1.TransID, t1.TenantID, t1.TransactionType, t1.Amount, t1.TransactionDate,  max(t2.TransactionDate)
from table as t1 left outer join table as t2 on (t1.TenantID=t2.TenantID)
Where t1.TransactionType =1 and t2.TransactionType =2
and t1.TransactionDate > t2.TransactionDate

Answer 3

你需要一个子查询，或者你需要使用 MAX 和字符串函数做一些魔术，以便在一个查询中做得更好，或者你需要一个唯一标识前一个事务的 id。

select t.*, MAX(prevt.transid) as prev_trans_id
from transactions t 
left join transactions prevt
on t.tenantid=prevt.tenantid 
and t.transid > prevt.transid
group by t.transid

Answer 4

另一种方法：

SELECT
    T1.TransID,
    T1.TenantID,
    T1.TransactionType,
    T1.Amount,
    T1.TransactionDate,
    T2.TransactionDate AS PreviousPaymentDate
FROM
    Transactions T1
LEFT OUTER JOIN Transactions T2 ON
    T2.TenantID = T1.TenantID AND
    T2.TransactionType = 2 AND
    T2.TransactionDate < T1.TransactionDate
LEFT OUTER JOIN Transactions T3 ON
    T3.TenantID = T1.TenantID AND
    T3.TransactionType = 2 AND
    T3.TransactionDate < T1.TransactionDate AND
    T3.TransactionDate > T2.TransactionDate
WHERE
    T3.TransID IS NULL AND
    <your normal selection criteria>

您基本上获得同一租户的类型 2 的最后一个事务 (T2)，其中该租户的其他事务和相同类型 2 的事务在该事务之后不会出现（这就是 T3.TransID IS NULL 的作用 -如果没有找到匹配项，则它为 NULL 的唯一方法）。您还可以使用 NOT EXISTS 更清楚地做到这一点，但使用双 LEFT OUTER JOIN 的性能通常会更好。这是一个不存在的版本：

SELECT
    T1.TransID,
    T1.TenantID,
    T1.TransactionType,
    T1.Amount,
    T1.TransactionDate,
    T2.TransactionDate AS PreviousPaymentDate
FROM
    Transactions T1
LEFT OUTER JOIN Transactions T2 ON
    T2.TenantID = T1.TenantID AND
    T2.TransactionType = 2 AND
    T2.TransactionDate < T1.TransactionDate
WHERE
    NOT EXISTS (
        SELECT *
        FROM Transactions T3
        WHERE
            T3.TenantID = T2.TenantID AND
            T3.TransactionType = 2 AND
            T3.TransactionDate < T1.TransactionDate AND
            T3.TransactionDate > T1.TransactionDate
        ) AND
    <your normal selection criteria>