mysql group by last_MySQL group by last entry

问题

My table payment_status have these fields:

id

payment_id

status

created

created_by

Many entries could have the same payment_id... So, I want to get the last status for one 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

回答1:

Try this query -

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;

...then add WHERE conditions you need.

回答2:

Try to use JOIN:

SELECT p1.*

FROM payment_status p1

INNER JOIN

(

SELECT id, MAX(created) MaxCreated

FROM payment_status

WHERE status = '200'

GROUP BY id

) p2 ON p1.id = p2.id AND p1.created = p2.MaxCreated

ORDER BY p1.payment_id DESC

回答3:

this should work

SELECT *

FROM payment_status

WHERE status = '200'

ORDER BY created DESC

LIMIT 1

回答4:

shouldn't yo ujust be able to do this?:

(assuming that created is the timestamp, so "last" = "most recent")

SELECT pstatus.*

FROM `payment_status` AS pstatus

GROUP BY pstatus.payment_id, pstatus.status

ORDER BY pstatus.payment_id DESC, pstatus.created DESC

each row returns should have the payment_id with the most recent status.

回答5:

I suppose you are using MySQL database.

Here I have a solution is fast and readable:

select

substring_index(

group_concat(status order by created desc)

, ',', 1

) as status_latest

from payment_status

group by payment_id

I'm quite sure it's fast than others SQL statement,

You may try it.

来源：https://stackoverflow.com/questions/12512952/mysql-group-by-last-entry