mysql - mysql获取最近7次失败交易的所有卡片记录

Question

我有三张桌子 cards (id,...) order_cards (card_id, order_id....) transactions (order_card_id, status, ....)

我想获取最近 7 次交易失败的所有卡

我正在尝试类似的事情

select  cards.*,
(
select sum(case tmp1.status when 'fail' then 1 else 0 end) from  
(select transactions.status from order_cards
left join transactions on transactions.order_card_id = order_cards.id
where order_cards.card_id = cards.id
order by transactions.id desc limit 7
) as tmp1
) as total_fail
from cards

group by cards.id
having total_fail > 5

收到此错误

 Unknown column 'cards.id' in 'where clause'

上述查询的问题是父 ID 在 2 级子查询中不起作用。尝试使用有 count 子句，但它不适用于限制任何建议谢谢

已经尝试过

在子查询的子查询中使用父查询的列

MYSQL - 获取具有相同ID的超过1条记录的所有记录

标签： mysql

Answer 1

这应该有效。 Select c.*, SUM(t.status = 'FAILED') AS failed_txns from cards c, order_cards oc, transactions t where t.status='FAILED' and oc.order_id = t.order_card_id and c.id = oc.card_id group by c.id having failed_txns >= 7