MySQL -根据条件获取每个成员的最后记录

Question

我需要一些帮助来查询：

Table Customers
id        name       email
1         john       john@abc.com
2         doe        doe@abc.com

Table Membership
id      facility       package
1       a              x
2       b              y
3       c              z

Table Orders
id      orderid        result            customerid      membershipid
1       order-1        Unsuccessful      1               1
2       order-2        Successful        2               1
3       order-3        Successful        1               1
4       order-4        Successful        1               2
5       order-5        Unsuccessful      1               2

我想要达到的是得到最后的订单，对每一个会员的客户来说都是不成功的。

这意味着在这种情况下，查询应该只返回order-5。最后一次获得会员资格的记录是成功的。

到目前为止，我已经使用这个查询，但它返回最大的不成功的记录，不服从最后的记录。

我希望我已经解释了我的问题。

以下是查询

SELECT o.transactionresult FROM orders o where o.transactionresult='Unsuccessful' group by o.membershipid ORDER by o.id DESC

Answer 1

如果最后一次是指最后一个id，那么您可以使用NOT存在：

SELECT o.* 
FROM orders o 
where o.transactionresult='Unsuccessful'
and not exists (
  select 1 from orders
  where membershipid = o.membershipid and id > o.id
)

见演示。

如果您想要每个客户的成员资格：

SELECT o.* 
FROM orders o 
where o.transactionresult='Unsuccessful'
and not exists (
  select 1 from orders
  where membershipid = o.membershipid and customerid = o.customerid and id > o.id
)

见演示。

结果：

| id  | orderid | transactionresult | customerid | membershipid |
| --- | ------- | ----------------- | ---------- | ------------ |
| 5   | order-5 | Unsuccessful      | 1          | 2            |

Answer 2

您可以为此使用聚合：

select o.customer, o.membership, max(o.id)
from orders o
group by o.customer, o.membership
having max(o.id) = max(case when o.result = 'Unsuccessful' then o.id end);

这将返回客户/成员资格组合，其中最大id是不成功的最大id。

如果您想要整行，我将使用关联子查询：

select o.*
from orders o
where o.result = 'Unsuccessful' and
      o.id = (select max(o2.id)
              from orders o2
              where o2.customer = o.customer and o2.membership = o.membership
             );

这将检查结果是否不成功，并且id是客户/成员身份组合的最大id。