最近有一种需求，一张订单可能有多个支付单，这就要求我们拿到每一张订单的最新支付单。具体思路如下：
[En]
Recently, there is a demand that there may be multiple payment orders for an order, which requires us to get the latest payment order for each order. The ideas are as follows:
最近有一种需求，一张订单可能有多个支付单，这就要求我们拿到每一张订单的最新支付单。具体思路如下：
[En]
Recently, there is a demand that there may be multiple payment orders for an order, which requires us to get the latest payment order for each order. The ideas are as follows:
写一个子查询，该子查询负责查询每个订单最新的支付单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.htmOriginal: https://www.cnblogs.com/lm66/p/15693728.html
Author: Liming_Code
Title: GROUP BY 后获取每一组最新的一条记录
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/507954/
转载文章受原作者版权保护。转载请注明原作者出处！