mysql查询一个表里面会员消费金额的重复值，列举出每个会员的最多重复字段

select id 会员id,count(*) as count '消费金额重复次数', (Price-amount) as '消费金额'
,from consumption
where id=22724 group by (Price-amount)
order by count desc limit 1

因为是用排序做的，所以只能查出单个会员的消费金额

我想要达到的结果(查出每个会员消费金额重复次数最多的一类，
例如id为22724的会员一共消费了9次，但是有8次都是消费200元，所以显示次数为8次，消费金额为200)
id 次数 消费金额
22724 8 200
22723 9 300
22722 7 100

感觉有点麻烦了，用到了关联子查询。
用两个窗口函数也可以玩

select b.id,b.(Price-amount),b.count
from (select a.id,a.(Price-amount),count,row_number() over( PARTITION by id,(Price-amount) order by count desc ) as rn
from (select id,(Price-amount),count(1) as count
from consumption
group by id,(Price-amount) ) a)b
where b.rn =1