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

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

《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
最佳答案

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

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