使用 mysql 显示前 4 行 Rank 显示错误
问题描述：
我需要显示 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