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

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

《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
《G01 Go 实战入门》
从零开始带你一步步开发一个 Go 博客项目,让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。
最佳答案

可以先通过分组查询到每个分组 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

和预期一致。

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

盲猜用 mysql8 的窗口函数

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

可以先通过分组查询到每个分组 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

和预期一致。

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

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

10个月前 评论

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