GROUP BY 后获取每一组最新的一条记录

GROUP BY 后获取每一组最新的一条记录

最近有个需求,一个订单可能会存在多个支付单,需要我们获取每个订单最新的支付单。思路如下:
写一个子查询,该子查询负责查询每个订单最新的支付单ID,然后和支付单表进行内关联查询。
情况一:数据库主键自增情况,取ID最大的那条记录

SELECT id,
       xx,
       xxx,
       xxxx,
       xxxxx
FROM txxx t1,
     (SELECT MAX( id ) AS id FROM txxx GROUP BY order_id ) AS t2
WHERE t1.id = t2.id

情况二:数据库主键是UUID,无法比较,利用创建时间字段,取时间最近那条记录

SELECT id,
       xx,
       xxx,
       xxxx,
       xxxxx,
       create_time
FROM txxx t1,
     (SELECT SUBSTRING_INDEX(GROUP_CONCAT(id order by create_time desc),",",1) AS id FROM txxx GROUP BY order_id ) AS t2
WHERE t1.id = t2.id

或者将子查询放进IN里

SELECT id,
       xx,
       xxx,
       xxxx,
       xxxxx
FROM txxx t1,
WHERE t1.id IN (SELECT MAX( id ) AS id FROM txxx GROUP BY order_id)

还有一种思路:先按照时间或ID倒序排序,然后再分组,这样取到的每一组都是最新的

SELECT id,
       xx,
       xxx,
       xxxx,
       xxxxx
FROM ( SELECT * FROM txxx ORDER BY id DESC ) t
GROUP BY
    order_id

参考链接:https://blog.csdn.net/qq_35069223/article/details/84343961
     https://www.jb51.net/article/23969.htm