使用 mysql 显示前 4 行 Rank 显示错误 [英] To display top 4 rows using mysql Rank is displaying wrong

问题描述

我需要显示 top4 并根据 Amount 和按 agentId 分组租用 4 行，但这里的排名显示错误以及如何显示最少(最后 4 行?)架构:

I need to display the top4 and lease 4 rows based Amount and group by agentId but here rank is showing wrong And how to show least(last 4 rows?) schema:

 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;

推荐答案

试试这个:

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

这篇关于使用 mysql 显示前 4 行 Rank 显示错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！