问题
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