本文介绍了MySQL按最后一个条目分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的表payment_status具有以下字段:
My table payment_status have these fields:
- id
- payment_id
- 状态
- 创建
- created_by
许多条目可能具有相同的payment_id ...所以,我想获取一个Payment_id的最新状态...
Many entries could have the same payment_id... So, I want to get the last status for one payment_id...
我有这个要求,但需要花太多时间来加载...我想有一个优化版本来按Payment_id分组并取得最后状态.
I have this request that work but take too much time to load... I would like to have an optimize version to group by payment_id and take the last status.
SELECT pstatus.*
FROM `payment_status` AS pstatus
WHERE pstatus.id = (
SELECT id
FROM `payment_status`
WHERE pstatus.status = '200'
ORDER BY created DESC
LIMIT 1
)
GROUP BY pstatus.payment_id
ORDER BY pstatus.payment_id DESC
推荐答案
尝试此查询-
SELECT t1.* FROM payment_status t1
JOIN (SELECT payment_id, MAX(created) max_created
FROM payment_status
GROUP BY payment_id
) t2
ON t1.payment_id = t2.payment_id AND t1.created = t2.max_created;
...然后添加所需的WHERE条件.
...then add WHERE conditions you need.
这篇关于MySQL按最后一个条目分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!