mysql - 使用 mysql 显示前 4 行排名显示错误

Question

我需要根据 AgentId 和 Amount 分组显示 top4 和 lease 4 行，但这里的排名显示错误以及如何显示最少(最后 4 行？) 模式:

 AgentID amount 
    1      3000
    1      3200
    2      9000
 SELECT Agentid,SUM(AmountRecevied) as Amount,@rownum := @rownum + 1 AS Rank
 FROM collection ,(SELECT @rownum := 0) r
 GROUP BY AgentID
 ORDER BY Amount DESC
 limit 4;

Answer 1

试试这个方法:

SELECT T.Agentid,T.Amount, @rownum := @rownum - 1 AS Rank
 FROM
 (SELECT Agentid,SUM(AmountRecevied) as Amount
 FROM collection 
 GROUP BY AgentID
 ORDER BY Amount
 LIMIT 4) T,(SELECT @rownum := 11) r