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

select user_id, max(amount) from table group by user_id

select * from table group by user_id order by amount desc limit 1

楼上都都不对哈，group by 出来的id都是最小的那一个

select o.* from orders o where amount = (select max(amount)  from orders where uid = o.uid)

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

SELECT * FROM orders WHERE amount in (SELECT max(amount) FROM orders GROUP BY user_id );

SELECT id,user_id, max(amount) FROM table GROUP BY user_id

MySQL8.0+ 使用窗口函数

 SELECT user_id,  MAX(amount) as max_amount
 FROM table_name
 GROUP BY user_id
 ORDER BY amount DESC;

select max(amount), user_id from amount GROUP BY user_id

SELECT
    *
FROM
    orders 
WHERE
    amount IN ( SELECT max( amount ) FROM orders GROUP BY user_id );

这个问题涉及的知识点，分组中取最大、最小记录

-- id user_id amount
-- 解法1
SELECT * FROM  orders 
where amount in (
SELECT MAX(amount) as max_amount FROM orders GROUP BY user_id 
) GROUP BY user_id;

-- 解法2
SELECT * FROM  orders  a INNER JOIN
(
SELECT user_id, MAX(amount) as max_amount FROM orders GROUP BY user_id 
) b  on a.user_id=b.user_id and a.amount=b.max_amount
GROUP BY a.user_id;

-- 解法3
-- mysql 8 以上用窗口函数
SELECT * FROM 
(SELECT *,rank() OVER (PARTITION by user_id ORDER BY amount desc ) as `rank` FROM orders ) a
where a.rank=1;

这里考察的是先排序分组的问题,mysql默认分组是已经排序的了,是升序,这里明显要降序 SELECT id, user_id, account FROM ( SELECT * FROM order ORDER BY account desc LIMIT 100 ) as t1 GROUP BY user_id