讨论数量:
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
-- 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;
推荐文章: