MySQL按最后一个条目分组 [英] MySQL group by last entry

问题描述

我的表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按最后一个条目分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！