mysql 如何查询每个用户消费金额最大的记录？

面试时遇到一个问题 查询每个用户消费金额 表结构[id, user_id, amount]

img

我想的是:

SELECT
    user_id,
    max( amount ) 
FROM
    orders 
GROUP BY
    user_id

但看到社区很多人给的语句都是使用了子查询。为什么要写这么复杂？
比如:

SELECT
    o.* 
FROM
    orders o
RIGHT JOIN (
    SELECT
        max(amount) mxa,
        user_id 
    FROM
        orders 
    GROUP BY
        user_id 
    ) m ON o.user_id = m.user_id 
AND o.amount = m.mxa

因为查询的字段不一样。如果你只是查询最大金额是可以的，但是还要查询订单其他相关信息的话，你的写法不适合。
由于使用了group by分组。查询的普通字段必须在分组字段中。

SELECT
    o.* 
FROM
    orders o
RIGHT JOIN (
    SELECT
        max(amount) mxa,
        user_id 
    FROM
        orders 
    GROUP BY
        user_id 
    ) m ON o.user_id = m.user_id 
AND o.amount = m.mxa

这种方式是针对于现实场景而言的，查询每个用户消费金额对应的订单记录信息。
实现的逻辑：就是子查询找到每个用户最大的订单金额，然后根据订单金额去关联匹配，找到对应的订单记录。

想的好，但是实际上不可行的，你的sql会出现所有的用户消费金额都是一样的，而且max的值是这些数据中最大的，你并没有让max运用合理，让他比较每一个用户的消费记录，这样依赖就必须先进行分组，每一组代表一个用户的消费记录然后从每一个组中获取这个用户的最大消费记录

如果你还需要对后续结果进行筛选，使用子查询有时候更方便，直接在后面加where条件即可
其实group by 后面也可以再继续加having来限制查询范围
但如果你后面还想要left join个别的什么表，直接做就办不到了

"每个用户消费金额最大的记录" 当然是指的

SELECT
    user_id,
    max( amount ) 
FROM
    orders 
GROUP BY
    user_id

这绝对没有任何问题，但是，原问题真的是这个么？
如果原问题是，查询每一笔订单的记录，并顺便带出对应订单的用户历史所有订单中最高的一笔金额

select o.*,
max(amount ) over(partintion by user_id ) max_amount 
from orders o;

不过，开窗函数在mysql里，得8.0以上版本才支持

另外，你第二个sql，查的其实是每个用户最高金额的订单id，但这样查的话，同一个用户最高金额如果有两笔订单，也会一起展现出来

其实你可以这么理解,我想查order表中的其他信息,比如订单时间, 订单人等等时候,如果还是按照你的方法直接查询字段数据信息时候, 会对这个表做全查, 假如15万条记录, 全查会慢一些,  如果是后者查询, 就会先做筛选查询记录id 和金额最大的,这时候只查了两个字段,此时作为临时表A,相对能接受速度一些, 这时候使用Right join右查询, 由于  临时表A只有一条记录, 和整个表关联查询时候 在查询某些字段信息是不是速度更快一些呢?  我是这么理解的