如何在groupby的每组获取最新的那条,子查询orderby desc也无法生效, 子查询distinct倒是可以,但是这个是正经写法吗

如何在groupby的每组获取最新的那条,子查询orderby desc也无法生效, 子查询distinct倒是可以,但是这个是正经写法吗

《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程,JWT 概念及使用 和 API 开发相关的进阶知识。
最佳答案

可以先通过分组查询到每个分组 ID 最新的记录,然后再连接本表,查询得到 ID 对应的记录详情。

例如有用户购买记录表user_purchase,结构如下:

名称 字段名
id 主键 ID
uid 用户唯一 ID
order_id 订单唯一 ID
pay_time 下单时间

表中记录如下:

id uid order_id pay_time
1 1 100 2024-06-01 14:00:00
2 1 101 2024-06-02 14:00:00
3 2 102 2024-06-04 15:00:00
4 2 103 2024-06-03 15:00:00
5 3 104 2024-06-05 16:00:00
6 3 105 2024-06-05 16:00:00

现在需要统计每个用户最新的一条下单记录。

可以通过group by结合联表查询获取到结果,SQL 如下:

SELECT
    t2.* 
FROM
    ( SELECT uid, MAX( pay_time ) AS pay_time FROM user_purchase GROUP BY uid ) t1
    JOIN user_purchase t2 ON t1.uid = t2.uid 
    AND t1.pay_time = t2.pay_time

查询结果如下:

id uid order_id pay_time
2 1 101 2024-06-02 14:00:00
3 2 103 2024-06-04 15:00:00
5 3 105 2024-06-05 16:00:00
6 3 106 2024-06-05 16:00:00

这里查询出的结果和我们预期的基本一致。唯一有点疑问的是,uid 为 3 的用户出现了两条记录,这是因为这两条记录的购买时间相同,所以都被取出来了。如果想只取一条记录的话,这里只需要再定义一条排序规则,然后限制获取即可。

例如,如果存在相同时间的记录,取 id 较大的记录,SQL 如下:

SELECT
    t4.* 
FROM
    (
SELECT
    t2.uid AS uid,
    max( id ) AS id 
FROM
    ( SELECT uid, MAX( pay_time ) AS pay_time FROM user_purchase GROUP BY uid ) t1
    JOIN user_purchase t2 ON t1.uid = t2.uid 
    AND t1.pay_time = t2.pay_time 
GROUP BY
    t2.uid 
    ) t3
    JOIN user_purchase t4 ON t3.uid = t4.uid 
    AND t3.id = t4.id

结果如下:

id uid order_id pay_time
2 1 101 2024-06-02 14:00:00
3 2 103 2024-06-04 15:00:00
6 3 106 2024-06-05 16:00:00

和预期一致。

1个月前 评论
快乐的皮拉夫 (作者) 1个月前
renji566 (楼主) 1个月前
讨论数量: 7
sanders

盲猜用 mysql8 的窗口函数

1个月前 评论
renji566 (楼主) 1个月前

可以先通过分组查询到每个分组 ID 最新的记录,然后再连接本表,查询得到 ID 对应的记录详情。

例如有用户购买记录表user_purchase,结构如下:

名称 字段名
id 主键 ID
uid 用户唯一 ID
order_id 订单唯一 ID
pay_time 下单时间

表中记录如下:

id uid order_id pay_time
1 1 100 2024-06-01 14:00:00
2 1 101 2024-06-02 14:00:00
3 2 102 2024-06-04 15:00:00
4 2 103 2024-06-03 15:00:00
5 3 104 2024-06-05 16:00:00
6 3 105 2024-06-05 16:00:00

现在需要统计每个用户最新的一条下单记录。

可以通过group by结合联表查询获取到结果,SQL 如下:

SELECT
    t2.* 
FROM
    ( SELECT uid, MAX( pay_time ) AS pay_time FROM user_purchase GROUP BY uid ) t1
    JOIN user_purchase t2 ON t1.uid = t2.uid 
    AND t1.pay_time = t2.pay_time

查询结果如下:

id uid order_id pay_time
2 1 101 2024-06-02 14:00:00
3 2 103 2024-06-04 15:00:00
5 3 105 2024-06-05 16:00:00
6 3 106 2024-06-05 16:00:00

这里查询出的结果和我们预期的基本一致。唯一有点疑问的是,uid 为 3 的用户出现了两条记录,这是因为这两条记录的购买时间相同,所以都被取出来了。如果想只取一条记录的话,这里只需要再定义一条排序规则,然后限制获取即可。

例如,如果存在相同时间的记录,取 id 较大的记录,SQL 如下:

SELECT
    t4.* 
FROM
    (
SELECT
    t2.uid AS uid,
    max( id ) AS id 
FROM
    ( SELECT uid, MAX( pay_time ) AS pay_time FROM user_purchase GROUP BY uid ) t1
    JOIN user_purchase t2 ON t1.uid = t2.uid 
    AND t1.pay_time = t2.pay_time 
GROUP BY
    t2.uid 
    ) t3
    JOIN user_purchase t4 ON t3.uid = t4.uid 
    AND t3.id = t4.id

结果如下:

id uid order_id pay_time
2 1 101 2024-06-02 14:00:00
3 2 103 2024-06-04 15:00:00
6 3 106 2024-06-05 16:00:00

和预期一致。

1个月前 评论
快乐的皮拉夫 (作者) 1个月前
renji566 (楼主) 1个月前

先分组用户查各自最新id 再内联原表数据 就是用户最新

1个月前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!