MySql分组查询最新的多有记录

6次阅读

共计 610 个字符，预计需要花费 2 分钟才能阅读完成。

数据库表结构

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

查询语句

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  
     on p1.id = p2.id  
    and p1.update_date = p2.UpdateDate  
where   
    1 = 1  
order by p1.update_date desc