MySql group query for the latest multi-record

Posted Jun 15, 20201 min read

1. Query all the latest order information of the specified user

Database table structure
id user_id order_status update_date
0001 admin ORDER_BUILD 2020-06-01
0002 admin ORDER_PAYED 2020-06-02
0003 admin ORDER_DELEVERY 2020-06-03
0004 root ORDER_BUILD 2020-06-01
0005 admin ORDER_ARRIVEL 2020-06-04
check sentence
select * from

(
Select * from tc_order
Where user_id ='admin'
) p1
inner join
(
Select
Id, user_id, order_status, max(update_date) UpdateDate
From tc_order
Where
User_id ='admin'
Group by id
) p2
P1.id = p2.id
And p1.update_date = p2.UpdateDate
where
1 = 1
order by p1.update_date desc

search result
id user_id order_status update_date
0004 root ORDER_BUILD 2020-06-01
0005 admin ORDER_ARRIVEL 2020-06-04