mysql - SQL查询以查找最近的记录是否都是同一类型

Question

我有一个transaction 表，其架构如下

id | order_id | response | amount
1  | 2        |'payment' | 1000
2  | 5        |'declined'| 0
3  | 5        |'declined'| 0
4  | 5        |'payment' | 500
5  | 5        |'declined'| 0
6  | 11       |'declined'| 0
7  | 11       |'declined'| 0
9  | 11       |'declined'| 0

我想要做的是找到所有订单，其中该订单的三个最近的交易是“拒绝的”。假设 id 越高，交易越新(或者您可以假设有一个 created_at 列)。

在上述情况下，唯一应该返回的 order_id 是 11，因为虽然 order_id 5 有 3 次拒绝交易，但最近的 3 次交易是 D P D

是否有一种干净的方法可以使用在合理的时间内运行的纯 sql 执行此操作(假设约 5000 万行)。

Answer 1

假设 id 越高越新:

SELECT t0.order_id 
FROM transaction t0 
JOIN transaction t1 ON
   ((t1.response=t0.response) AND (t1.order_id=t0.order_id) AND 
   t1.id=(SELECT MAX(id) FROM transaction WHERE id<t0.id and t0.order_id=order_id)) 
JOIN transaction t2 ON 
   ((t2.response=t0.response) AND (t2.order_id=t0.order_id) AND 
   t2.id=(SELECT MAX(id) FROM transaction WHERE id<t1.id AND t0.order_id=order_id)) 
WHERE t0.response='declined' AND 
   t0.id=(SELECT MAX(id) FROM transaction WHERE order_id=t0.order_id);