mysql - 每组选择四个项目？

Question

我有两个表，第一个名为card:

| card_id | card_name |

以及表提款:

| withdrawal_id | card_id | transaction_date |

我想获取每个card_id的最后四个transaction_dates。

我尝试了这段代码，但它不会给出最后 4 个日期:

SELECT a.card_id, b.transaction_date
FROM card AS a 
JOIN withdrawal AS b ON a.card_id = b.card_id
GROUP BY a.card_id, b.transaction_date
ORDER BY a.card_id, b.transaction_date    

我需要改变什么？

Answer 1

您似乎想要获取每组的前 N ​​行，这可以使用如下方法完成:

SELECT a.card_id, b.transaction_date
FROM card a
JOIN withdrawal b ON a.card_id = b.card_id
WHERE(
   SELECT COUNT(*)
   FROM card c
   JOIN withdrawal w ON w.card_id = c.card_id
   WHERE c.card_id = a.card_id AND w.transaction_date <= b.transaction_date
) <= 4
ORDER BY a.card_id, b.transaction_date

查看此question有关获取每组行的更多信息。